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

[gopikrishnach]

Hi All,

How to load the latest qvd from the folder.

ex: i have  qvds in that folder like:

                                                     sample_20190331_0.qvd

                                                     sample_20190331_1.qvd

                                                     sample_20190331_2.qvd

                                                     sample_20190430_0.qvd

                                                     sample_20190430_1.qvd

                                                     sample_20190530_0.qvd

                                                     sample_20190530_1.qvd

i want to load that particular  the month wise Latest number .qvd ,not all,into my app.

like for March i want to load sample_20190331_2.qvd, for April i want load sample_20190430_1.qvd

how to achieve this?

[jwjackso]

Try:

Set vEOM=Date(MonthEnd(Now()),'YYYYMMDD');

DATA:

load *

from  $(sample_vEOM.qvd) (qvd);

[gopikrishnach]

Here i cann't use monthend function as i have multiple files with  same date with different versions, so i need to consider the last number also.

And i want load all month filea one by one. 

Ex:

Sample_march_max(Number). Qvd then

Sampme_April_max(Number). Qvd then

Sample_may_max(Number). Qvd..

Iike this.

[danilostochi]

Hi, see if this helps you.

//temp:
//LOAD MaxString(PurgeChar(filebasename(),'abcdefghijklmnopqrstuvwxyz_')) as dir from dados_0101201*.qvd(qvd);

temp_: LOAD MaxString(filebasename()) as dir_ from dados_0101201*.qvd(qvd);
temp: LOAD MaxString(dir_) as dir Resident temp_; DROP table temp_;

let vDir = Peek('dir');

result:
LOAD id,
nome
FROM C:\Users\Danilo\Documents\'$(vDir)'.qvd(qvd);

[gopikrishnach]

Hi Danil,

I tried your Code, but in my scenario the QVD's are created month wise and with version wise. I have to load the all qvd's at time by considering the max version number also.

Please help me how to load.

[gopikrishnach]

I want load only below QVD's from the list.

For March Latest version qvd >>sample_20190331_2.qvd
April Latest version QVD>>>>>>>sample_20190430_1.qvd
May Latest version QVD>>>>>>>>>sample_20190530_1.qvd

[danilostochi]

hi, try implementing with this function at creation to qvd.

https://help.qlik.com/pt-BR/qlikview/November2017/Subsystems/Client/Content/Scripting/FileFunctions/...

[sunny_talwar · Accepted solution]

You can try something like this

Table:
LOAD * INLINE [
    Dim
    1
];

STORE Table into sample_20190331_0.qvd (qvd);
STORE Table into sample_20190331_1.qvd (qvd);
STORE Table into sample_20190331_2.qvd (qvd);
STORE Table into sample_20190430_0.qvd (qvd);
STORE Table into sample_20190430_1.qvd (qvd);
STORE Table into sample_20190530_0.qvd (qvd);
STORE Table into sample_20190530_1.qvd (qvd);

DROP Table Table;

Table:
LOAD Date#(SubField(FileBaseName(), '_', 2), 'YYYYMMDD') as Date,
	 FileName() as FileName,
	 SubField(FileBaseName(), '_', 3) as Version
From *.qvd (qvd);

Right Join (Table)
LOAD Date,
	 Max(Version) as Version
Resident Table
Group By Date;

FOR i = 1 to FieldValueCount('FileName')

	LET vFileName = FieldValue('FileName', $(i));
	
	TRACE $(vFileName);
	
	FinalTable:
	LOAD Dim
	From $(vFileName) (qvd);
	
NEXT

DROP Table Table;

[aditi-dhooria]

Try the following code:

temp1:
LOAD
filebasename() as dir_,

SubField(filebasename(), '_', 3) as dir_num

from [d:\qlik\from\qvd\qvd dynamic load\Sample_*.qvd](qvd);

temp2:
LOAD
FileName&'_'&Max_number as FileName;
LOAD
subfield(dir_, '_', 1) &'_'& subfield(dir_, '_', 2) as FileName,
Max(subfield(dir_, '_', 3)) as Max_number
Resident
temp1
Group by
subfield(dir_, '_', 1) &'_'& subfield(dir_, '_', 2)
;

Drop Table temp1;

Let v1 = NoOfRows('temp2');

for i = 0 to $(v1)-1
let vDir = Peek('FileName',$(i),'temp2');

Concatenate
result:
LOAD *,
'$(vDir)' as FileName1
from [d:\qlik\from\qvd\qvd dynamic load\*.qvd](qvd);

next i;

[vittorianad]

hi,

this code can help you to load all files

DIRECTORY [path\yourPath];
for each File in filelist('*.qvd')

concatenate(yourTable)
LOAD field1,
            field2,
            ...
FROM [$(File)] (qvd);
next File;

DIRECTORY [];

but it will not solve your load selection.

Probably you can integrate with where condition on some max value...