MAX(TOTAL <key> date) question

[amien]

LOAD * INLINE [
 key, case, date
 1, 100, 1-1-2010
 1, 101, 1-2-2010
 1, 102, 1-3-2010
 1, 103, 1-4-2010
 1, 104, 1-5-2010
];

see also attachment .. subject gives me the result that i need in a pivot. only one issue:

on case = 104, i don't want the max date to be 1-5-2010 .. but 1-4-2010.

so, if the date is the same as the max date within the same key, then it should move on the the next max date within the key.

 <table><col></col> <col></col> <tbody><tr><td>case</td><td>MAX(TOTAL <key> date)</td></tr><tr><td>100</td><td>1-5-2010</td></tr><tr><td>101</td><td>1-5-2010</td></tr><tr><td>102</td><td>1-5-2010</td></tr><tr><td>103</td><td>1-5-2010</td></tr><tr><td>104</td><td>1-4-2010</td></tr></tbody></table>

[pover]

Amien,

Can you please clarify your source data and expected result. In the attached QVW the source data is

LOAD * INLINE
key, case, date
1, 100, 1-1-2010
1, 101, 1-2-2010
1, 102, 1-3-2010
2, 103, 1-4-2010
2, 103, 1-4-2010
];

which doesn't correspond with your post.

Regards, Karl

[amien]

oeps .. wrong attachment uploaded .. here is the correct one .. (=same as text in first post)

[pover]

Thanks Amien,

One more question. Why should 100, 101, 102 and 103 have '1-5-2010' and 104 '1-4-2010'? What makes 104 special?

[amien]

104 is special cause that record contains the max date value within the same key.

so for the 104, there should be a special max calculation : calculate the max date within the same key, WITHOUT using the date of 104. calculation shouldnt take the date value 104 into consideration.

the max date result will be 1-4-2010 (= value of 103)

[pover]

How about this expression?

if(date=MAX(TOTAL <key> date),MAX(TOTAL <key> date,2),MAX(TOTAL <key> date))

[amien]

ofcouse, rank = 2 .. thanks

but what if i want the case value corresponding with the date in an expression?

so that expression whould contain '104' in all cases, except for the last one (103)

[pover]

I don't know if I follow, but how about

if(case=MAX(TOTAL <key> case),MAX(TOTAL <key> case,2),MAX(TOTAL <key> case))

Regards.

[amien]

thats not what i meant. date=MAX(TOTAL <key> date) is fine.

the result that i need is not the date, but the corresponding caseid from MAX(TOTAL <key> case)

case MAX(TOTAL <key> date) expression?

100 1-5-2010 104
101 1-5-2010 104
102 1-5-2010 104
103 1-5-2010 104
104 1-4-2010 103