Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
I hope your'e familiar with the machanics and commands of Qlik so you'll be able to do so.
BR,
Eliran.
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.
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