Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello ,
I have to create a dashboard in Qliksense where:-
-It should refresh only 2 months data and rest historical data is kept same in the report.
-I am aware that we have to use incremental load but not able to understand how should i implement it in this situation.
Please advice.
Thanks
Hey,
As you mentioned incremental load is used to get the new data into the existed dashboard.
Step1:
For that you need an one date field or numberic key field to perform the incremental load. So find it out the date field first in your script.
Step 2:
Find the max of date field
Max(Datefield) as Max_Date
Step3:
Store the value into the variable
Let vMax_Date = Peek('Max_Date',0,'Tablename')
Step 4: load the new data with where condition of
where Date >= '$(vMax_Date)'
For example find the below code
Load:
LOAD Id,
Name,
Product,
Cost,
UnitsInStock,
UnitsOnOrder,
[Last Date]
FROM
[..\import\lastmodify.xlsx]
(ooxml, embedded labels, table is Load);
Incrementalload:
LOAD max([Last Date]) as Max_date1
Resident Load;
LET VMax_Date= Peek('Max_date1', 0 , 'Incrementalload');
Concatenate(Load)
LOAD Id,
Name,
Product,
Cost,
UnitsInStock,
UnitsOnOrder,
[Last Date]
FROM
[..\import\lastmodify.xlsx]
(ooxml, embedded labels, table is Load)
Where last date > $(VMax_Date);
Regards,
DurgadeviKumar
Thank you for the detailed explanation. It is very helpful.
Just one thing, since i want to update data only for 2 months , shouldn't i be using Max(Date)-2 or something. Not clear on that.
Please advice.
Many thanks
Hi,
As per requirement, you want to update the last 2 month data by using incremental load.
So, you could use the new field with the help of addmonth function
addmonth (Maxdatefield,-2) as newfield, pass those value in to the variable
And in where condition
where date > '$(Newfield)'
so the data should be updated from newfield date onwards,
For example max date= 06/05/2020 // max date
after addmonth= 04/05/2020 // two month before the max date
Hope it is helpful
Regards,
DurgadeviKumar
I would
for reload = 0 to 1
let startdate = monthstart(addmonths(today(),-$(reload));
let enddate = monthend(addmonths(today(),-$(reload));
let filename = monthname('$(startdate)');
datas:
sql select
yourfields
from yourtable
where datefield >= '$(startdate)' and datefield <= '$(enddate)';
store datas into [lib://lib/filename_$(filename).qvd] (qvd);
drop table datas;
next
so the datas are stored into a file with monthname in.
then you can load
* from
[lib://lib/filename_*.qvd] (qvd);
into your datas that all months are in.
For an inital relaod, set variable reload to a value that old values are all created.
Regards
Maybe you have to format your date syntax from startdate and enddate, depending on your database
Thank You for your response.
Since i am extracting data from multiple databases like SAP HANA and SQL , should i apply incremental load at extraction level ?