Reload only 2 months data in the dashboard

[Aspiring_Developer]

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

[Durgadevikumar]

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

[Aspiring_Developer]

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

[Durgadevikumar]

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

[martinpohl]

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

[Aspiring_Developer]

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 ?