How to load last 7 days files dynamically

[Rsaiq]

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.

[Vegar]

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

[Rsaiq]

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

[Vegar]

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