Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mahitham
Contributor

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

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.


1 Solution

Accepted Solutions
rangam_s
Contributor II

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

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



4 Replies
rangam_s
Contributor II

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

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



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

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
Contributor

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

Hi Avinash,

Thanks for your reply.

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

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

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

Community Browser