topic Re: Max Date issue in QlikView

Max Date issue

Anonymous — Mon, 30 Nov 2015 14:47:17 GMT

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

Re: Max Date issue

JonnyPoole — Mon, 30 Nov 2015 17:15:45 GMT

Try this

Datatable:

LOAD

max([Accounting Period]) as maxaccountingperiod

FROM

(qvd)

);

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

Re: Max Date issue

tamilarasu — Tue, 01 Dec 2015 02:21:47 GMT

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;

Re: Max Date issue

Anonymous — Tue, 01 Dec 2015 05:13:13 GMT

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

Re: Max Date issue

Kushal_Chawda — Tue, 01 Dec 2015 05:43:06 GMT

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;

Re: Max Date issue

engishfaque — Tue, 01 Dec 2015 06:02:28 GMT

Dear Chandrasekar,

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

Kind regards,

Ishfaque Ahmed

Re: Max Date issue

Anonymous — Tue, 01 Dec 2015 07:22:23 GMT

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.