Load a date in Inline Table

inline_tab:

load date(test,'YYYY-MM-DD') AS TEST inline [

test

'2012-01-01'

];

Inline Load With Inputfield

Inputfield Cancelled;
Inputfield Comments;
Inputfield Reason;

load 'F1' & recno() as Division,
'F2' & recno() as Person,
'F3' & recno() as [Appointment Date],
'' as Cancelled,
'' as Comments,
'' as Reason
autogenerate 50;

QlikView Inline tables with Null values

QlikView Inline tables with Null values

Sometimes you need a quick INLINE table for testing or just for a lookup table. In some of these cases you will need NULL values in your INLINE table. When you want to use NULL values in your INLINE table, you can use the following code:

//first set null interpreter to the string: NULL
SET NULLINTERPRET=NULL;

Orders:
LOAD * INLINE [
    OrderID, OrderDate
    112233, 1/2/2008
    223344, 2/2/2008
    334455, NULL
    445566, NULL
    556677, 3/3/2008
];

//reset null interpreter
SET NULLINTERPRET=;

Quite simple, but easy to forget.

How to handle single quotes in Inline Load

You can enclose field names in [ ], ` `, ´ ´ and " " pairs. So try it like this:

LOAD * INLINE [

    F1, F2

    Actuals, sum({$<"Source Name" = {'Current View'}>}"Outlook Units")

];

Or if you have no double quotes in your expressions try:

LOAD * INLINE "

    F1, F2

    Actuals, sum({$<[Source Name] = {'Current View'}>}[Outlook Units])

";

Inline Tips & Tricks #2

http://qlikboard.com/2011/10/12/nifty-trick-for-inline-tables/

Nifty trick for INLINE tables

October 12th, 2011 by Bill LayLeave a reply »

Here’s a nifty little trick I discovered by accident when creating INLINE tables…

Some Motivation

Typically when I’m creating an INLINE table it’s for one of two reasons:

1. I’m working very quickly and need to generate some additional data.
2. I’m building a control table to use as part of the ETL script. For example, setting up a loop and I want to pull a value from the INLINE table into each loop iteration.

The technique I’m going to demonstrate is relevant to usage #1 above. In fact, it’s only useful if I’m going to base my INLINE table (in part) on data that already exist in my data model.

Consider this specific example:

• We are building a Global Sales Analysis application
• We have a Country field in our associative model
• We wish to also analyze sales by Region: {EMEA, APAC, Americas} … but we don’t have a data source for this

What’s the easiest way to “append” the Region onto each Country record …. ???

The Trick

In the past, when faced with the use case as described above, I would do the following:

1. Create a List Box on Country.
2. Then right-click and copy possible values.
3. Then paste those values into Excel for further manipulation. Or more typically paste them right into the INLINE load wizard.

BUT, there is a simpler way to achieve this, if you take note of a curious little “Tool” menu inside the Load INLINE table wizard…

Step One

Make sure the source field already exists in the data model. In our example it’s the Country field.

Step Two

Go into the Script Editor (ctrl-E) and go to the Inline table wizard:

Insert > Load Statement > Load Inline

Step Three

Check out the Tools menu inside the Inline Data Wizard window

Click on it and select the Document Data… option

Step Four

Locate the source field in the list of Available Fields. You will have an option to select All Values (this is what I always end up using) or take advantage of the associative model if selections have already been made in the dashboard.

Step Five

The wizard then populates the values from the source field into the first column of the table

Step Six

The second (and subsequent) columns can now be manually populated. Which in our use case is adding the Region {EMEA, APAC, Americas}

The Final Product

Clean it up and reload. And the final result should look like this:

I hope you find a chance to use this shortcut in your future projects.

Keep on Qlikin’

Inline Syntax

http://qlikviewdev.blogspot.com/2013/01/inline-table.html

The Inline is a interesting concept in QlikView. This will be useful when the data is static and it will avoid a  database hit . 

Syntax:
load [ distinct ] *fieldlist
[( from file [ format-spec ] | 
from_fieldfield [format-spec] 
inline data [ format-spec ] ] 
[ where criterion ] | while criterion ]
[ group_by fieldlist ] 
[order_by field [ sortorder ] { , field [ sortorder ] } ] 

Example:
We all know the budgeting system has only three accounting types (Actual, Budget and Projection) for analysing the data. So if we want to show it in the dropdown select or list box we can hard code it using the Inline table.

Inline:

Load * Inline [Account_Type

Actual,

Budget,

Projection];

Output:

Table: Inline

Account_Type

Actual

Budget

Projection

As the data is already known and static this Inline function saves a database hit.

Load order with Inline (Video)