Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have a question an need some little help
I could work well with this load script a long time.
DataSelection:
LOAD Distinct FilePath() AS FileName,
FileTime() AS DateFile
FROM D:\Qlikview11\Tools\APITool_v3_Qlikview\reports\28781_20*.csv; //Anomalie ?!
LET FileNameOfSites = peek('FileName',-1);
LoadingFile:
LOAD
.........
....
..
FROM '$(FileNameOfSites)' (txt, codepage is 1252, embedded labels, delimiter is ';', msq);
This snippet of code could recognize the latest file based on the filename and select it for load. Unfortunately, the name of the generated sourcefiles are changed
an the above script does no longer works.
Now i want to select the files based on timestamp instead of the filename.
Can you adapt my loadscript that only the latest file (timestamp) is selected?
Thank you for help.
I think the easiest way is to add this line before the LET statement:
INNER JOIN (DataSelection) LOAD max(DateFile) as DateFile RESIDENT DataSelection;
It will reduce the DataSelection table to only one record (assuming there are no files with the same timestamp), so there is no need to change anything else.
Regards,
Michael
I think the easiest way is to add this line before the LET statement:
INNER JOIN (DataSelection) LOAD max(DateFile) as DateFile RESIDENT DataSelection;
It will reduce the DataSelection table to only one record (assuming there are no files with the same timestamp), so there is no need to change anything else.
Regards,
Michael
HI Michael,
thank you for your advice. I build it in my code. It seems to work fine. But can I write it below or Is this all right?
TEMP_PICKER_SITES:
LOAD Distinct FilePath() AS FileName,
FileTime() AS DateFile
FROM D:\Qlikview11\Tools\APITool_v3_Qlikview\reports\28781_20*.csv;
INNER JOIN (TEMP_PICKER_SITES) LOAD max(DateFile) as DateFile RESIDENT TEMP_PICKER_SITES;
LET FileNameOfSites = peek('FileName',-1);
DROP TABLE TEMP_PICKER_SITES;
TEMP_PICKER_CAMPAIGN:
LOAD Distinct FilePath() AS FileName,
FileTime() AS DateFile
FROM D:\Qlikview11\Tools\APITool_v3_Qlikview\reports\28779*.csv;
INNER JOIN (TEMP_PICKER_CAMPAIGN) LOAD max(DateFile) as DateFile RESIDENT TEMP_PICKER_CAMPAIGN;
LET FileNameOfCampaign = peek('FileName',-1);
DROP TABLE TEMP_PICKER_CAMPAIGN;
TEMP_PICKER_CAMPAIGNEXT:
LOAD Distinct FilePath() AS FileName,
FileTime() AS DateFile
FROM D:\Qlikview11\Tools\APITool_v3_Qlikview\reports\111573_201*.csv;
INNER JOIN (TEMP_PICKER_CAMPAIGNEXT) LOAD max(DateFile) as DateFile RESIDENT TEMP_PICKER_CAMPAIGNEXT;
LET FileNameOfCampaignExt = peek('FileName',-1);
DROP TABLE TEMP_PICKER_CAMPAIGNEXT;
Thanks a million.
best regards,
Looks reasonable. You don't need the second parameter anymore in the pick() function because there is only one record in the table, but if you leave as is it will work anyway.
Regards,
Michael