Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Logic for Max Date

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

3 Replies
santiago_respane
Specialist
Specialist

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

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;

View solution in original post

tmumaw
Specialist II
Specialist II
Author

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