Get the corresponding Value of the Max Month in all the records

[sk88024]

Hi,

I have a requirement, where I want to get the Value against the Max of the Month for all the records. I am able to achieve this using a straight chart, with an expression - 

Aggr(If(Month = Max(TOTAL <ID> Month), Sum(Value)), ID, Month)

However, I want to achieve the same using QlikView script, without using any charts or set analysis. Any suggestions please?

This is my input data. 

ID	Month	Value
1	4	123
1	5	123
1	6	777
1	7	777
1	8	777
2	8	4245
2	9	4245
2	10	2756
2	11	2756
3	9	498
3	10	498
3	11	498
3	12	999

And, this is my output. 

ID	Month	Value
1	4	777
1	5	777
1	6	777
1	7	777
1	8	777
2	8	2756
2	9	2756
2	10	2756
2	11	2756
3	9	999
3	10	999
3	11	999
3	12	999

Thank you!

SK

[Aurelien_Martinez · Accepted solution]

Hi,

With your example :

data:
LOAD
*
Inline [
ID, Month, Value
1, 4, 123
1, 5, 123
1, 6, 777
1, 7, 777
1, 8, 777
2, 8, 4245
2, 9, 4245
2, 10, 2756
2, 11, 2756
3, 9, 498
3, 10, 498
3, 11, 498
3, 12, 999
];

LOAD
ID,
Month,
Value,
If(ID=Peek(ID),
Peek(NewValue),
Value
) as NewValue
Resident data
Order By
ID,
Month desc
;

DROP Table data;

Aurélien

[rwunderlich · Accepted solution]

I would recommend FirstSortedValue. 

Join (data)
Load
  ID,
  FirstSortedValue(Value, -Month) as NewValue
Resident data
Group By ID
; 

You can also use FirstSortedValue as a chart expression. 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com