topic Re: Preserve data based on reload date in App Development

Preserve data based on reload date

iswarya — Mon, 31 Aug 2020 05:31:55 GMT

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

Re: Preserve data based on reload date

eliran — Mon, 31 Aug 2020 06:59:04 GMT

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

First time, load the complete data and store to qvd.
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.

Re: Preserve data based on reload date

iswarya — Mon, 31 Aug 2020 09:32:30 GMT

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.

Re: Preserve data based on reload date

bhavyagrb — Mon, 31 Aug 2020 14:33:56 GMT

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