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)

Inline Tips & Tricks

http://michaelellerbeck.com/2008/08/25/qlikview-inline-data/

So if you ever need to embed some data, like if you wanted to define a week to quarter relationship, the Inline Data Wizard is the way to go. Click the Inline Wizard button and then type in your data.

This will make you something that looks like this

LOAD * INLINE [
Month, Quarter
1, Q1
2, Q1
3, Q1
4, Q1
5, Q2
6, Q2
7, Q3
8, Q3
9, Q3
10, Q4
11, Q4
12, Q4
];

Note! Empty cells in Load inline statements represent an ‘empty string’ rather than null.

http://www.qlikviewaddict.com/2012/03/inline-load-trick.html

Inline Load Trick

Even the newest of QV developers have likely used an inline load and be familiar with the below screen. For those that might not have come across them, inline loads allow you to define a table of data within the QV script. You can use to wizard to create them which is available from the Edit Script dialog by using the menu, Insert -> Load Statement -> Load Inline.

There is however a feature of the Inline Data Wizard that often gets overlooked. If you look you will notice a Tools menu containing only a single tool, "Document Data...". Clicking on this opens up a new window like this:

This gives you the option of inserting values from existing fields in the data model. Please note, QlikView can't guess what the values will be when the script is next run so will show you the fields and values currently available in the app from the last time the script was run successfully. Clicking OK will paste the values into the Inline Data Wizard window.

And it's as simple as that. I most commonly use inline loads to create small, static mapping tables and this obviously means taking data from the existing data model and mapping them to groups or new values. This little trick is perfect for this and can save you having to create a temporary list box in the front end t copy the values from.

What is Inline Load

Inline Load:

You can load data from file or can define the data within Qlikview and load (Inline) from there. The inline data can be defined in the Inline Data Wizard as: Insert > Load Data > Inline Data.

e.g.

LOAD * INLINE [

    Display as

    Dollars

    Percentage

];


Inline is used if data should be typed within the script, and not loaded from a file. Use the Inline Data Wizard for help with the creation of load inline statements.
data ::= [ text ]

Data entered through an inline clause must be enclosed by double Quotation Marks or with square brackets. The text between these is interpreted in the same way as the content of a file. Hence, where you would insert a new line in a text file, you should also do it in the text of an inline clause, i.e. by pressing the Enter key when typing the script. 

Inline Data Wizard

The Inline Data Wizard dialog is opened from the Insert menu, Load Statement, Load Inline. It is used to create load inline statements in the script.
The dialog contains something looking like a spreadsheet and in fact works like one in most respects. Note however that calculation formulas will not be evaluated in this spreadsheet as they would e.g. in Microsoft Excel.
Each column represents a field to be loaded into QlikView by means of an inline table. Each row is a record in the table. A data cell is selected by clicking it. A value may then be typed or pasted in from clipboard. Press Enter or an arrow key to accept the value and move to another cell.
The top (label) row is reserved for field labels. Double-click in a label cell to edit it. If no values are entered in the label row the field names F1, F2 etc will be used.

Edit Menu

The Edit menu contains some basic editing commands.

Insert Column	Inserts a new blank column if one or more columns are selected.
Delete Column	Deletes the selected column(s).
Insert Row	Inserts a new blank row if one or more rows are selected.
Delete Row	Deletes the selected rows.

Tools Menu

The Tools menu contains the command Document Data that opens the Import Document Data Wizard from which it is possible to paste field values from any existing field in the document. In the dialog it is possible to see how many field values are present in any selected field. It is also possible to choose to insert all values, selected values or excluded values into the inline wizard.

Click OK to close the dialog and generate a Load inline statement reflecting the contents of the dialog's table grid.