Qlik Community

Qlik Sense Enterprise Documents

Documents for Qlik Sense related information.

Qlik Sense 2.2 - Preparing Data - Part 2

Employee
Employee

Qlik Sense 2.2 - Preparing Data - Part 2

Adding data to Qlik Sense is very easy, however Qlik Sense does not know your data until you allow it to prepare it for use within the app. There are a few ways to prepare data with Qlik Sense. This video is part 2 of a two part series, which demonstrates an 'advanced' approach when adding data to a Qlik Sense app. This video shows you what happens when you select 'Prepare Data'. Samples and the video .mp4 file are attached below.

Part 1: Qlik Sense 2.2 - Preparing Data - Part 1

NOTE: You can use the expanded date dimension functionality with existing apps prior to 2.2, buy using the DECLARE and DERIVE expressions in your Data Load script.

Example:

[autoCalendar]:

  DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),

  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),

  Month($1) AS [Month] Tagged ('$month'),

  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),

  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

  Date(Floor($1)) AS [Date] Tagged ('$date');

DERIVE FIELDS FROM FIELDS [OrderDate] USING [autoCalendar] ;

http://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/Declar...

Derived fields ‒ Qlik Sense

Comments
prassinoux
New Contributor

How can I take advantage of the new Date feature on existing apps with data prepared via load script?

Employee
Employee

Excellent question Pascal!

You can do this using the DERIVE and DECLARE functions.

Here is an example of what is being auto-generated when using the Data Load Wizard and "Prepare Data" - I'll attach the sample app to the original post, you should see it along with the rest of the files.

[autoCalendar]:

  DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),

  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),

  Month($1) AS [Month] Tagged ('$month'),

  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),

  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

  Date(Floor($1)) AS [Date] Tagged ('$date');

DERIVE FIELDS FROM FIELDS [OrderDate] USING [autoCalendar] ;

http://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/Declar...

Derived fields ‒ Qlik Sense

Version history
Revision #:
1 of 1
Last update:
‎02-23-2016 08:52 AM
Updated by:
Employee