Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Can any one please help me on below requirement.
In a Folder I have the files like below
SALEIN_InputBranch_20220101.csv
SALEIN_InputBranch_20220102.csv
SALEIN_InputBranch_20220103.csv
'
'
SALEIN_InputBranch_20220131.csv
Daily some files are updating into this folder randomly.
Now loading these files as SALEIN_InputBranch_*csv
But here need to load only last 7 days files from the folder.
From filename need to extract the date and from maximum date need to load last 7 days files.
I need only below files to be loaded in qliksense :-
SALEIN_InputBranch_20220125.csv
SALEIN_InputBranch_20220126.csv
SALEIN_InputBranch_20220127.csv
SALEIN_InputBranch_20220128.csv
SALEIN_InputBranch_20220129.csv
SALEIN_InputBranch_20220130.csv
SALEIN_InputBranch_20220131.csv
Please help me on this.
Thanks in advance.
You can try to use For each ... in filelist() to get better control of your file names. I have tried to type down an example. I have not checked the syntax or spelling, but it should give you an idea on how it could be solved.
for each _file in filelist('LIB://DATA/SALEIN_InputBranch_????????.csv')
FileDates:
Load
subfield(subfield('$(_file)', '_',-1),'.',1) as FileDate
Autogenerate 1;
next _file
MaxDate:
Load max(FileDate) as MaxFileDate
Resident FileDates;
Drop table FileDates;
Let vMaxFileDate = date#(peek('MaxFileDate',-1,'MaxDate'),'YYYYMMDD');
Drop table MaxDate;
for i = 0 to 6
Let vFilename = 'SALEIN_InputBranch_'&date('$(_date)'-$(_i),'YYYYMMDD')&'.csv';
Load * From 'LIB://DATA/$(vFilename)';
next
Thanks @Vegar for you reply.
I used the script but its showing me that FileDates not found error.
I have checked all the syntax its correct but i am getting run time error.
Tried with myself but didnt get any idea for this error,could you please help me on this issue.
Thanks in advance
It sound like your for loop is not generating any values You need to adjust this part to fit your setup. (You need to adjust the adress to your own LIB and check that your files matches the pattern. You could probably swap the ???????? wildchars with a * .
for each _file in filelist('LIB://DATA/SALEIN_InputBranch_????????.csv')
FileDates:
Load
'$(_file)' as File, //Check if you get any values here
subfield(subfield('$(_file)', '_',-1),'.',1) as FileDate
Autogenerate 1;
next _file