Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"))
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%;