How to load Max(Date) and Max(Date)-1 Qvds

[mahitham]

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.

[rangam_s · Accepted solution]

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

[avinashelite]

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

[mahitham]

Hi Avinash,

Thanks for your reply.

The above script is not working. Not getting the date records in the Temp_Date field.

[avinashelite]

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