Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ay
New Contributor

Filtering Date in Load Script

Hey everyone,

I am new to QlikSense and having trouble at loading data with date filters. I have tried this code but i couldn't find the right syntax. Could u please help on it?

 


LIB CONNECT TO 'PROLAXRUN_DB (cb_77280)';

LOAD "AS_OF_DATE",
Year("AS_OF_DATE"),
Month("AS_OF_DATE"),
Day("AS_OF_DATE"),
    "REGION_CODE"   
Where ("AS_OF_DATE" = MakeDate(31/12/2018)
or "AS_OF_DATE" = MakeDate(31/03/2019)
or "AS_OF_DATE" = MakeDate(30/06/2019)
or "AS_OF_DATE" = Max("AS_OF_DATE")-1
or "AS_OF_DATE" = Max("AS_OF_DATE"))

Labels (2)
1 Reply
Partner
Partner

Re: Filtering Date in Load Script

If you are pulling from a database you will need to use SQL first to query the data from it. Also, I tend to format the dates as a number when used in the logic that way I don't have to worry about comparing dates with different formats.

 

Also, notice the MakeDate() function. It takes year, month, day parameters. 

 

LIB CONNECT TO 'PROLAXRUN_DB (cb_77280)';

MaxDate:
Load
   Num(Floor(MAX_AS_OF_DATE)) as MaxDate //Format as number to make easier
;
SQL SELECT
    Max("AS_OF_DATE") as MAX_AS_OF_DATE
FROM 
    %PUT_DATABASE_TABLE_HERE%;

Let vMaxDate = Peek('MaxDate',0,'MaxDate'); //Store value in variable
Let vMaxDate_1 = $(vMaxDate)-1;

Drop Table MaxDate;

Data:
LOAD 
    "AS_OF_DATE",
    Year("AS_OF_DATE") as AS_OF_YEAR,
    Month("AS_OF_DATE") as AS_OF_MONTH,
    Day("AS_OF_DATE") as AS_OF_DAY,
    "REGION_CODE"   
Where 
    WildMatch(Num(Floor("AS_OF_DATE")),Num(MakeDate(2018,12,31)), Num(MakeDate(2019,03,31)),Num(MakeDate(2019,06,30)), '$(vMaxDate)','$(vMaxDate_1)') > 0
;
SQL SELECT 
    "AS_OF_DATE",
    "REGION_CODE"
FROM 
    %PUT_DATABASE_TABLE_HERE%;

 

Blog: WhereClause   Twitter: @treysmithdev