Preserve data based on reload date

[iswarya]

Hi,

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

E.g: 

ID	Name
1	A
2	B
3	C
4	D
5	E

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)

6	F
44	G
45	H
46	I

The final output will be like:

ID	Name	Reloaded Date
1	A	30-Aug
2	B	30-Aug
3	C	30-Aug
4	D	30-Aug
5	E	30-Aug
6	F	31-Aug
44	G	31-Aug
45	H	31-Aug
46	I	31-Aug

[eliran]

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]

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]

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