topic Re: Logic for Max Date in QlikView

Logic for Max Date

tmumaw — Mon, 08 Aug 2016 17:29:09 GMT

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

Re: Logic for Max Date

santiago_respane — Mon, 08 Aug 2016 17:38:03 GMT

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,

Re: Logic for Max Date

sunny_talwar — Tue, 09 Aug 2016 02:30:46 GMT

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;

Re: Logic for Max Date

tmumaw — Tue, 09 Aug 2016 10:27:53 GMT

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