Logic for Max Date

[tmumaw]

Hello All.....

I am trying to extract some data from SAP and need to only include the MAX date for one date and need the others to come with it.  I am getting a couple of dates for CallDate and a  couple of dates for CompDate, where all I am looking for is the max date for PlanDate and the corresponding Call and Comp dates.  Any idea what I am doing wrong?

Thanks

Temp:
Load
if(mid(MntPlan_WARPL,1,5) = 'PMH/J',mid(MntPlan_WARPL,1,5),
if(mid(MntPlan_WARPL,1,5) = 'PMC/D',mid(MntPlan_WARPL,1,5),
if(mid(MntPlan_WARPL,1,3) = 'PMN',mid(MntPlan_WARPL,1,3),
if(mid(MntPlan_WARPL,1,3) = 'PML',mid(MntPlan_WARPL,1,3),
if(mid(MntPlan_WARPL,1,3) = 'PMA',mid(MntPlan_WARPL,1,3)))))) as PM_Type, 
if(mid(MntPlan_WARPL,1,5) = 'PMH/J',mid(MntPlan_WARPL,6,7),
if(mid(MntPlan_WARPL,1,5) = 'PMC/D',mid(MntPlan_WARPL,6,7),
if(mid(MntPlan_WARPL,1,3) = 'PMN',mid(MntPlan_WARPL,4,7),
if(mid(MntPlan_WARPL,1,3) = 'PML',mid(MntPlan_WARPL,4,7),
if(mid(MntPlan_WARPL,1,3) = 'PMA',mid(MntPlan_WARPL,4,7)))))) as Equipment_No,
MntPlan_WARPL as MaintPlan,
Max(Date(PlanDate_NPLDA))  as PlanDate,
//Max(Date([Call date_HORDA])) as CallDate,
[Call date_HORDA]   as CallDate,
//Max(Date(Compl.Date_LRMDT)) as CompDate
Compl.Date_LRMDT   as CompDate
Resident MHIS
where Year(PlanDate_NPLDA) >= '$(vCYear)'
Group by MntPlan_WARPL, PlanDate_NPLDA, [Call date_HORDA], Compl.Date_LRMDT

[santiago_respane]

Hi,

i don't know if i got your point maybe you can share some data in order to identify your need based on your source data.

Maybe this helps:

Temp:

LOAD *,Max(Date(PlanDate_NPLDA))  as PlanDate

Group by PM_Type, Equipment_No, CallDate, CompDate;

Load

if(mid(MntPlan_WARPL,1,5) = 'PMH/J',mid(MntPlan_WARPL,1,5),

if(mid(MntPlan_WARPL,1,5) = 'PMC/D',mid(MntPlan_WARPL,1,5),

if(mid(MntPlan_WARPL,1,3) = 'PMN',mid(MntPlan_WARPL,1,3),

if(mid(MntPlan_WARPL,1,3) = 'PML',mid(MntPlan_WARPL,1,3),

if(mid(MntPlan_WARPL,1,3) = 'PMA',mid(MntPlan_WARPL,1,3)))))) as PM_Type, 

if(mid(MntPlan_WARPL,1,5) = 'PMH/J',mid(MntPlan_WARPL,6,7),

if(mid(MntPlan_WARPL,1,5) = 'PMC/D',mid(MntPlan_WARPL,6,7),

if(mid(MntPlan_WARPL,1,3) = 'PMN',mid(MntPlan_WARPL,4,7),

if(mid(MntPlan_WARPL,1,3) = 'PML',mid(MntPlan_WARPL,4,7),

if(mid(MntPlan_WARPL,1,3) = 'PMA',mid(MntPlan_WARPL,4,7)))))) as Equipment_No,

MntPlan_WARPL as MaintPlan,

[Call date_HORDA]   as CallDate,

Compl.Date_LRMDT   as CompDate

Resident MHIS

where Year(PlanDate_NPLDA) >= '$(vCYear)';

Please let me know.

Kind regards,

[sunny_talwar · Accepted solution]

How about this:

Temp:
LOAD If(Mid(MntPlan_WARPL, 1, 5) = 'PMH/J', Mid(MntPlan_WARPL, 1, 5),
           If(Mid(MntPlan_WARPL, 1, 5) = 'PMC/D', Mid(MntPlan_WARPL, 1, 5),
           If(Mid(MntPlan_WARPL, 1, 3) = 'PMN', Mid(MntPlan_WARPL, 1, 3),
           If(Mid(MntPlan_WARPL, 1, 3) = 'PML', Mid(MntPlan_WARPL, 1, 3),
           If(Mid(MntPlan_WARPL, 1, 3) = 'PMA', Mid(MntPlan_WARPL, 1, 3)))))) as PM_Type,

           If(Mid(MntPlan_WARPL, 1, 5) = 'PMH/J', Mid(MntPlan_WARPL, 6, 7),
           If(Mid(MntPlan_WARPL, 1, 5) = 'PMC/D', Mid(MntPlan_WARPL, 6, 7),
           If(Mid(MntPlan_WARPL, 1, 3) = 'PMN', Mid(MntPlan_WARPL, 4, 7),
           If(Mid(MntPlan_WARPL, 1, 3) = 'PML', Mid(MntPlan_WARPL, 4, 7),
           If(Mid(MntPlan_WARPL, 1, 3) = 'PMA', Mid(MntPlan_WARPL, 4, 7)))))) as Equipment_No,

           MntPlan_WARPL as MaintPlan,
           Max(Date(PlanDate_NPLDA))  as PlanDate,

           FirstSortedValue([Call date_HORDA], -PlanDate_NPLDA)  as CallDate,

           FirstSortedValue(Compl.Date_LRMDT, -PlanDate_NPLDA) as CompDate
Resident MHIS
Where Year(PlanDate_NPLDA) >= '$(vCYear)'
Group By MntPlan_WARPL;

[tmumaw]

Thanks Sunny T.  I can always count on a correct response from you.  Have a great day.

P.S.   I have never used or heard of this.

           FirstSortedValue([Call date_HORDA], -PlanDate_NPLDA)  as CallDate,

           FirstSortedValue(Compl.Date_LRMDT, -PlanDate_NPLDA) as CompDate.

Thom