topic Re: Loading selective qvds from particular folder in QlikView

Loading selective qvds from particular folder

balaji_m — Wed, 28 Mar 2018 04:45:14 GMT

Hi ,

I want to load last 5 qvds created by monday.

Please find attached here with list of QVDs.

i want to load below qvds.based on today date.In file name WK_1 stands for monday file.

Load * From .\filename1_WK_1*.qvd...

I am able to filter monday qvds using above load statement.

filename1_WK_1_26_Feb_2018

filename1_WK_1_05_Mar_2018

filename1_WK_1_12_Mar_2018

filename1_WK_1_19_Mar_2018

filename1_WK_1_26_Mar_2018

Regards,

Balaji

Re: Loading selective qvds from particular folder

binujose1982 — Wed, 28 Mar 2018 05:13:50 GMT

Hi Balaji,

Please use below script;

SET dir="C:\Test\";

for each file in filelist(dir&'filename1_WK_1*.qvd')

tempTable:

LOAD *

FROM

$(file)

(qvd);

next file

Regards,

Binu

Re: Loading selective qvds from particular folder

jonathandienst — Wed, 28 Mar 2018 05:18:43 GMT

I am not sure what you want to know. You are already filtering for Monday as far as i can tell from your post.

Re: Loading selective qvds from particular folder

balaji_m — Wed, 28 Mar 2018 13:00:44 GMT

Hi Jonathan,

I want to load last 5 monday qvds.

Can you please let me know how to filter?

Regards,

Balaji

Re: Loading selective qvds from particular folder

boorgura — Wed, 28 Mar 2018 20:55:19 GMT

FileList:

NoConcatenate

LOAD * Inline [

File, Date

];

for Each vFile in FileList('filename1_WK_1_*.qvd')

Concatenate(FileList)

LOAD '$(vFile)' as File,

date(date#(left(right('$(vFile)', 15), 11), 'DD_MMM_YYYY')) as Date

AutoGenerate(1);

inner join(FileList)

First 5

LOAD

Date

Resident FileList

Order By Date desc;

FileList will have the latest 5 files, along with the corresponding Dates.

Re: Loading selective qvds from particular folder

jonathandienst — Thu, 29 Mar 2018 04:45:59 GMT

Scan the folder and store the results in reverse order, then load the first 5:

Set vFiles = '';

For Each vFile in FileList('.\filename1_WK_1*.qvd')

Let vFiles = vFile & If(Len(vFiles), ',' & vFiles);

Next

Table: // table stub for concatenation

LOAD '' as Source AutoGenerate 0;

For i = 1 To 5

Let vFile = SubField(vFiles, i);

If Len(vFile) > 0 Then

Concatenate(Table)

LOAD *, FileBaseName() as Source

FROM [$(vFile)] (ooxml, ...);

End If

Next