Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Can any one please help me on below requirement.
In C:/Qliksharefolder/4.QVDs path I have number of qvds with date like below.
kensitivities_Detail_20180520.qvd
Kensitivities_Detail_20180521.qvd
Kensitivities_Detail_20180616.qvd
Kensitivities_Detail_20180617.qvd
From these qvds I have to load only Max(date) and Max(date)-1 qvds i.e.,
Kensitivities_Detail_20180616.qvd
Kensitivities_Detail_20180617.qvd
dynamically in the load script
Please help me on this.
Thanks in advance.
try this
load
Max(date) as Mdate,
Max(date)-1 as Mdate_1
from [C:/Qliksharefolder/kensitivities_Detail*.qvd](qvd);
In case, if you want to consider date from file name then use below script:
date:
LOAD
date(date#(max(mid(FileName(),Index(FileName(),'Detail_')+7,8)),'YYYYMMDD'),'MM/DD/YYYY') as Date
FROM [C:/Qliksharefolder/kensitivities_Detail*.qvd] (qvd);
MaxDate:
load text(date(max(Date),'YYYYMMDD')) as MDate,
text(date(Max(Date)-1,'YYYYMMDD')) as MDate_1 Resident date;
let Vmaxdate=Peek('MDate',-1, 'MaxDate');
let Vmaxdate_1=Peek('MDate_1',-1, 'MaxDate');
Data1:
LOAD
*
FROM [C:/Qliksharefolder/kensitivities_Detail_$(Vmaxdate).qvd] (qvd);
LOAD
*
FROM [C:/Qliksharefolder/kensitivities_Detail_$(Vmaxdate_1).qvd] (qvd);
try this
load
Max(date) as Mdate,
Max(date)-1 as Mdate_1
from [C:/Qliksharefolder/kensitivities_Detail*.qvd](qvd);
In case, if you want to consider date from file name then use below script:
date:
LOAD
date(date#(max(mid(FileName(),Index(FileName(),'Detail_')+7,8)),'YYYYMMDD'),'MM/DD/YYYY') as Date
FROM [C:/Qliksharefolder/kensitivities_Detail*.qvd] (qvd);
MaxDate:
load text(date(max(Date),'YYYYMMDD')) as MDate,
text(date(Max(Date)-1,'YYYYMMDD')) as MDate_1 Resident date;
let Vmaxdate=Peek('MDate',-1, 'MaxDate');
let Vmaxdate_1=Peek('MDate_1',-1, 'MaxDate');
Data1:
LOAD
*
FROM [C:/Qliksharefolder/kensitivities_Detail_$(Vmaxdate).qvd] (qvd);
LOAD
*
FROM [C:/Qliksharefolder/kensitivities_Detail_$(Vmaxdate_1).qvd] (qvd);
Try like this
Temp:
Load Date(Date#(Right(FileName(),8),'YYYYMMDD'),'YYYYMMDD') as DateField as Temp_Date
from [C:/Qliksharefolder/kensitivities_Detail_*.qvd] (qvd)
;
Date:
Load Temp_Date as Date,
Resident Temp
Order By
Temp_Date
;
LET vMaxDate = Peek('Date',-1,'Date');
LET vSecMaxDate = Peek('Date',-1,'Date');
DATA:
LOAD
*
from [C:/Qliksharefolder/kensitivities_Detail_'$(vMaxDate)'.qvd] (qvd);
LOAD
*
from [C:/Qliksharefolder/kensitivities_Detail_'$(vSecMaxDate )'.qvd] (qvd);
Hi Avinash,
Thanks for your reply.
The above script is not working. Not getting the date records in the Temp_Date field.
Sorry my bad
Temp:
Load
//add any one field from the QVD, I simply assumed it as A
A,
Date(Date#(Right(FileBaseName(),8),'YYYYMMDD'),'YYYYMMDD') as Temp_Date
from [C:/Qliksharefolder/kensitivities_Detail_*.qvd] (qvd)
;
Date:
Load Temp_Date as Date
Resident Temp
Order By
Temp_Date
;
Date:
Load Temp_Date as Date
Resident Temp
Order By
Temp_Date
;
DROP Table Temp;
LET vMaxDate = Peek('Date',-1,'Date');
LET vSecMaxDate = Peek('Date',-2,'Date');
DATA:
LOAD
*
from [C:/Qliksharefolder/kensitivities_Detail_$(vMaxDate).qvd] (qvd);
LOAD
*
from [C:/Qliksharefolder/kensitivities_Detail_$(vSecMaxDate ).qvd] (qvd);