Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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,
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;
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