Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Max Date issue

Hi

In the below qvd i want to show latest Accounting (YYYYMM) Period in data in the dashboard.But i am getting error.

LOAD [ID Number],

     [Description],

     [Accounting Period]

  

FROM

(qvd)

where [Accounting Period]=max([Accounting Period]);

6 Replies
JonnyPoole
Employee
Employee

Try this

Datatable:

LOAD

     max([Accounting Period]) as maxaccountingperiod

FROM

(qvd)

);

let vMaxPeriod=      peek('maxaccountingperiod',0,'Datatable');

tamilarasu
Champion
Champion

Hi Chandrsasekar,

Try like,

Temp:

LOAD [ID Number],

     Description,

     [Accounting Period]

FROM

[YourQVDPath.qvd]

(qvd);

MaxPeriod:

Load

Max([Accounting Period]) as [MaxAccPeriod]

Resident Temp;

Let vMax = Peek('MaxAccPeriod');

NoConcatenate

Data:

Load *

Resident Temp where Match([Accounting Period],'$(vMax)');

Drop Table MaxPeriod , Temp;

Anonymous
Not applicable
Author

1) max([Accounting Period]) would give you numeric value which will not match to your this [Accounting Period] field.


Make sure both are into same format .


2) What exactly you want? Are you trying to fetch max year data? If yes, then try like:

LOAD [ID Number],

     [Description],

     [Accounting Period]

 

FROM

(qvd)

where year([Accounting Period])=year(Today());



Kushal_Chawda

try to have optimized load as below

MaxPeriod:

load date(max([Accounting Period]),'YYYYMM') as MaxPeriod

FROM

(qvd);

Data:

LOAD [ID Number],

     [Description],

     [Accounting Period]

 

FROM

(qvd)

where exists (MaxPeriod, [Accounting Period]);

drop table MaxPeriod;

engishfaque
Specialist III
Specialist III

Dear Chandrasekar,

Kindly find attached file, I hope you are looking for that one.

Kind regards,

Ishfaque Ahmed

Anonymous
Not applicable
Author

Thanks for all your input.

Is not there any simple way because this is simple where class.

I just want to load only maximum date (Ex:201511) from the QVD.