topic Re: how to dynamically load the latest qvd in the folder in qlikview? in QlikView

how to dynamically load the latest qvd in the folder in qlikview?

d_koti24 — Wed, 24 Jun 2015 13:24:15 GMT

Hi All,

How to load the latest qvd from the folder.

ex: i have 3 qvds in that folder like:

sample_03_2015.qvd

sample_04_2015.qvd

sample_05_2015.qvd

if i get sample_06_2015.qvd, i want to load that particular qvd ,not all,into my app.

how to achive this?

regards,

Re: how to dynamically load the latest qvd in the folder in qlikview?

alexandros17 — Wed, 24 Jun 2015 13:45:36 GMT

This is what you need (with a few changet from xlsx to qvd)

let path_Pallet = '..\..\qlik\brown\MyFile*.xlsx';

for each File in filelist (path_Pallet)
AAA:
LOAD '$(File)' as FileName AutoGenerate 1;
next File

Tab:
LOAD MaxString(FileName) as lastFile Resident AAA;
LET myFile = Peek('lastFile', 0, 'Tab');
DROP Table AAA, Tab;

MyOnlyTable:
LOAD * From $(myFile);

Re: how to dynamically load the latest qvd in the folder in qlikview?

d_koti24 — Wed, 24 Jun 2015 13:59:43 GMT

Thanks for ur quick response.

i Am getting like this? why?

Re: how to dynamically load the latest qvd in the folder in qlikview?

alexandros17 — Wed, 24 Jun 2015 14:09:49 GMT

Send me your script please ....

Re: how to dynamically load the latest qvd in the folder in qlikview?

Wed, 24 Jun 2015 14:10:03 GMT

Use FileList, FileName & QVDCreateTime to get the accurate latest files in the Folder. But we need to assume the The QVD file name always have some pattern.

SALESQVDNAME_01_2015.qvd

SALESQVDNAME_02_2015.qvd

LET vFolderPath = 'QVDFolderPath';

For Each vFile in FileList('$(vFolderPath).*qvd')

QVDFiles:

LOAD

'$(vFile)' AS QVDFILENAME ,

QVDCreateTime('$(vFile)') AS QVDCREATETIME

Autogenerate 1;

Next vFile

// FInd the Pattern

Pattern:

LOAD

QVDFILENAME ,

QVDCREATETIME,

Subfield(SubField(QVDFILENAME,'\',-1),'_',1) AS Pattern

Resident QVDFiles;

Drop Table QVDFiles;

Inner Join (Pattern) // This is give Latest Time stamp for the pattern

LOAD

Pattern ,

Max(QVDCREATETIME) AS QVDCREATETIME

Resident Pattern;

For i=1 to Fieldvaluecount('QVDFILENAME')

let vLatestQVDName = FieldValue('QVDFILENAME',$(i))

LOAD * FROM [vLatestQVDName] (qvd);

Next i

Drop Table Pattern;

Re: how to dynamically load the latest qvd in the folder in qlikview?

d_koti24 — Wed, 24 Jun 2015 14:42:56 GMT

now it's working fine,but i am not getting latest one.

actuvally in my qvd's this qvd also there sample_._..qvd

sample_03_2015.qvd

sample_04_2015.qvd

sample_05_2015.qvd

i am getting sample_._..qvd this qvd .

below usage table contains the days field ,based that field i was spliting the qvds,but thing is the days field contains some nulls also that's why sample_._..qvd get's created

my script :

Dates:

LOAD DISTINCT

left(DATE(days,'MM/DD/YYYY'),2) AS MONTH, YEAR(DATE(days)) AS YEAR

RESIDENT Usage;

LET vNoOfMthsYrs = NoOfRows('Dates');

LET vMonth1 = PEEK('MONTH', -1, 'Dates');

LET vYear1 = PEEK('YEAR', -1, 'Dates');

FOR i = 0 TO $(vNoOfMthsYrs) - 1

LET vMonth = PEEK('MONTH', $(i), 'Dates');

LET vYear = PEEK('YEAR', $(i), 'Dates');

SPACE_MONTHLY:

LOAD *,'' as tmp RESIDENT Usage

WHERE left(DATE(days,'MM/DD/YYYY'),2) = '$(vMonth)' AND YEAR(DATE(days)) = '$(vYear)';

STORE SPACE_MONTHLY into [\\ns\dev1\USERS\Koti\space\MONTHLY_QVDS\FreeSpace.$(vYear)_$(vMonth).qvd](qvd);

drop field tmp;

drop table SPACE_MONTHLY;

2ndtab:

latest qvd load:

let path_Pallet = '\\ns\dev1\USERS\Koti\space\MONTHLY_QVDS\*.qvd';

for each File in filelist (path_Pallet)

AAA:

LOAD '$(File)' as FileName AutoGenerate 1;

next File

Tab:

LOAD MaxString(FileName) as lastFile

Resident AAA;

LET myFile = Peek('lastFile', -1, 'Tab');

set file_1=MaxString(FileName);

DROP Table AAA, Tab;

MyOnlyTable:

LOAD * From $(myFile);

Re: how to dynamically load the latest qvd in the folder in qlikview?

alexandros17 — Wed, 24 Jun 2015 14:49:25 GMT

If I have understood your files name are something like

.... sample_03_2015.qvd

in this case try to combine year and month part of the file name in order to obtain for each file a field like:

201503 (so YYYYMM)

in this case:

AAA:

LOAD '$(File)' as FileName, the_new_field_with_format_YYYYMM as YM AutoGenerate 1;

next File

Tab:

LOAD MaxString(YM) as lastFile

Resident AAA;

LET myFile = Peek('lastFile', -1, 'Tab');

--> create again the file name from the field YYYYMM

DROP Table AAA, Tab;

MyOnlyTable:

LOAD * From $(myFile);

Re: how to dynamically load the latest qvd in the folder in qlikview?

d_koti24 — Wed, 24 Jun 2015 15:33:25 GMT

sorry i did n't get this clearly --- the_new_field_with_format_YYYYMM as YM

can you please explian me clearly.

Re: how to dynamically load the latest qvd in the folder in qlikview?

alexandros17 — Wed, 24 Jun 2015 15:39:19 GMT

In my first answer I wrote:

Tab:

LOAD MaxString(FileName) as lastFile

....

but your file format has not a natural sorting so the logic is to find the last file searching for the max year+month string.