Qlik Community

Qlik Sense Documents & Videos

Documents & videos about Qlik Sense.

Creating Month Range for While Clause to Simplify Loading

Partner
Partner

Creating Month Range for While Clause to Simplify Loading

Hi,

If we want to perform some filtering using WHERE Clause for MONTH wise means we can easily perform using the below code.

eg: For the Value(MMMMYY) 201810

LOAD

F1,

F2

FROM <Source> WHERE F2=201810;

The above one easy when we require to filter one or two months but in the case of more months what you will do ?

Eg:

I want to load the data month wise from 2016 Jan to 2018 Oct

For this we can use the below code

Step 1:

Like Master Calendar Get the min and Max range in our case MinDate is 2016 Jan and MaxDate is 2018 Oct

So I'm using MakeDate function to Creating the Date range and Assigning them to Variables.

LET vMinDate = num(MakeDate(2016,01));

LET vMaxDate = num(MakeDate(2018,10));

Step 2:

Generate Master Calender

TempCalendar:

LOAD

DATE($(vMinDate)+ RowNo()-1,'YYYYMMDD') AS "Date"

AutoGenerate

$(vMaxDate)- $(vMinDate)+1;

Step 3: Create Month Range using DISTINCT

Month_range:

LOAD Distinct YYYYMM;

LOAD

LEFT("Date",6) AS YYYYMM

Resident TempCalendar;

Step 4:

Use FOR Loop for required action.

FOR vMonth_R = 0 TO NoOfRows('Month_range')

     LET vMonth=PEEK('YYYYMM',$(vMonth_R),'Month_range');

     TRACE IterNo is $(vMonth_R);

     TRACE YYYYMM is $(vMonth);

          Table:    

               LOAD

               F1,

               F2

          FROM <Source> WHERE F2=$(vMonth);

NEXT


Using the above Qlik Script we can dynamically filter and Load the records while using WHERE clause.


Thanks,

Vijayaganesh Selvakumar

Version history
Revision #:
1 of 1
Last update:
‎2018-10-07 03:31 PM
Updated by: