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

[d_koti24]

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,

kd

[alexandros17 · Accepted solution]

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);

[d_koti24]

Thanks for ur quick response.

i Am getting like this? why?

[alexandros17]

Send me your script please ....

[#5]

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;

[d_koti24]

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;

NEXT

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);

[alexandros17]

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);

[d_koti24]

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

can you please explian me clearly.

[alexandros17]

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.