Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
gopikrishnach
Contributor

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

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?

1 Solution

Accepted Solutions

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

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;
12 Replies
jwjackso
Valued Contributor

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

Try:

 

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

DATA:

load *

from  $(sample_vEOM.qvd) (qvd);

 

 

gopikrishnach
Contributor

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

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
Contributor

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

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

 

 

20052019.gif

 

(44) 9 9993-3605 TIM, WhatsApp, danilo16stochi@hotmail.com
gopikrishnach
Contributor

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

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
Contributor

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

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
Contributor

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

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

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

(44) 9 9993-3605 TIM, WhatsApp, danilo16stochi@hotmail.com

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

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
New Contributor

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

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
New Contributor III

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

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...