Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
iswarya
Partner
Partner

Preserve data based on reload date

Hi,

How to preserve the data based on reload date of app?

E.g: 

IDName
1A
2B
3C
4D
5E

The following records are loaded on yesterday(Aug 30) so against this the data should be Aug 30.

When the data is loaded next day, the new records should have the reload data of the next day(E.g: 31-Aug)

6F
44G
45H
46I

 

The final output will be like:

IDNameReloaded Date
1A30-Aug
2B30-Aug
3C30-Aug
4D30-Aug
5E30-Aug
6F31-Aug
44G31-Aug
45H31-Aug
46I31-Aug

 

3 Replies
eliran
Creator III
Creator III

Hi,

 

What you aim for, is incremental load.

There are various ways to handle these types of loads, following your example, you need to perform the following

  1.  First time, load the complete data and store to qvd.
  2.  Plan the incremental load
    1. load max ID from qvd - put into variable
    2. select data from DB where ID>maxID
    3. concatenate the data from the qvd
    4. overwrite the qvd

I hope your'e familiar with the machanics and commands of Qlik so you'll be able to do so.

 

BR,

Eliran.

iswarya
Partner
Partner
Author

Hi,

My aim is to get the reloaded date of an app in a separate column. So the yesterday loaded data should have yesterday date in the reload date column and today's data(yesterday data should be in yesterday date only) should have today's reload date.

bhavyagrb
Partner
Partner

Hello Iswaryak,

Please find below code and just replace ReloadTime() into date(ReloadTime(),'DD-MMM') in the script to get the format '30-Aug'. Hope it helps you.

Script Below:

Set vfilename='lib://FolderPath/data1.qvd';
Let v1= if(FileSize('$(vfilename)')>0,1,0); //check whether qvd exists or not

//if qvd does not exist
If $(v1)=0 then

data:
Load *,
ReloadTime() as ReloadDate
;
LOAD
ID,
Name
FROM [lib://FolderPath/data1_file.xls]
(biff, embedded labels, table is Sheet1$);

store data into [lib://FolderPath/data1.qvd](qvd);

//max of ID from above data
temp:
Load max(ID) as Temp_ID Resident data;
//let vID=peek('maxID',0,'temp');
store temp into [lib://FolderPath/tempdate.qvd](qvd);
drop table temp;

else
// if qvd exits then load incremented data by taking the maximum date from existing data
tempdate:
load Temp_ID from [lib://FolderPath/tempdate.qvd](qvd);
Let MaxId = peek('Temp_ID',-1);
drop table tempdate;

incremental:
load *,
ReloadTime() as ReloadDate
;
LOAD
ID,
Name
FROM [lib://FolderPath/data1_file.xls]
(biff, embedded labels, table is Sheet1$) where ID>$(MaxId);

//concatenating the new data with old data

Concatenate
LOAD
ID,
Name,
ReloadDate
FROM [lib://FolderPath/data1.qvd]
(qvd);

store incremental into [lib://FolderPath/data1.qvd](qvd);


//Again taking the maximum ID for next incremental load
temp:
load max(ID) as Temp_ID Resident incremental;
store temp into [lib://FolderPath/tempdate.qvd](qvd);
drop table temp;

exit Script;

Regards,

Bhavya