Max Date

[tmumaw]

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

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,
date(Max([Current Date],'YYYY-MM-DD')) as Max_Date
Resident Temp
Group by Month([Current Date])
;

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

Drop Tables Temp, Temp1;

[sunny_talwar]

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;

[tmumaw · Accepted solution]

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;

[sunny_talwar]

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

[tmumaw]

Thanks Sunny.  Did not notice that.