Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
Try:
Set vEOM=Date(MonthEnd(Now()),'YYYYMMDD');
DATA:
load *
from $(sample_vEOM.qvd) (qvd);
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.
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);
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.
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
hi, try implementing with this function at creation to qvd.
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;
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;
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...