topic Max Date in QlikView

Max Date

tmumaw — Tue, 24 Jan 2017 15:31:56 GMT

Hi everyone,

I need to get the max date by profit center month and year. This is what I have tried, but only getting 2016 and I have 5 years worth of data in my temp file. What I am looking for is Profit Center, Jan, 2013/01/31, NoEmp, Profit Center Feb, 2013/02/28, NoEmp, etc. I cannot sum No. Emps because I need the exact count on that date.

Thanks

NoConcatenate
Final:
LOAD *
Resident Temp
where Exists(Max_Date,[Current Date]);

Drop Tables Temp, Temp1;

Re: Max Date

sunny_talwar — Tue, 24 Jan 2017 15:37:30 GMT

May be try this:

Temp:

LOAD *,

Month([Current Date]) as Month

LOAD [Cost Ctr_ZKOSTL] as [Profit Center],
date(Date_ZWKDATE, 'YYYY-MM-DD') as [Current Date],
EmpCt_ZEMPCT as NoEmp
FROM

(qvd);

Temp1:
LOAD Month,
date(Max([Current Date]),'YYYY-MM-DD') as Max_Date
Resident Temp
Group by Month;

NoConcatenate
Final:
LOAD *
Resident Temp
where Exists(Max_Date,[Current Date]);

Drop Tables Temp, Temp1;

Re: Max Date

tmumaw — Tue, 24 Jan 2017 15:37:43 GMT

Temp:
LOAD [Cost Ctr_ZKOSTL] as [Profit Center],
date(Date_ZWKDATE, 'YYYY-MM-DD') as [Current Date],
EmpCt_ZEMPCT as NoEmp
FROM

(qvd);

Temp1:
LOAD
Month([Current Date]) as Month,
Year([Current Date]) as Year,
date(Max([Current Date],'YYYY-MM-DD')) as Max_Date
Resident Temp
Group by Year([Current Date]), Month([Current Date])
;

NoConcatenate
Final:
LOAD *
Resident Temp
where Exists(Max_Date,[Current Date]);

Drop Tables Temp, Temp1;

Re: Max Date

sunny_talwar — Tue, 24 Jan 2017 15:41:04 GMT

It's great that you found your solution and although this is working, make sure you use these parenthesis carefully

Date(Max([Current Date]),'YYYY-MM-DD')) as Max_Date

Re: Max Date

tmumaw — Tue, 24 Jan 2017 15:43:27 GMT

Thanks Sunny. Did not notice that.