How to create aggregation on script?

[#1]

Hi everybody!

I'm trying to create an aggregation condition on script.

Basically, I want to check what is the last month with volume greater than 0 considering the total per Supplier independentelly of the material involved.

I need to group per supplier and check if the volume is greater to zero to consider as YTD, if false consider as NOTVALID month for calculations. It is needed to give a reference check and give a reference for the month (YTD or not)

I have the following example:

Month	Supplier	Product	Value
Jan-11	A	1	200
Jan-11	A	2	150
Jan-11	B	1	130
Feb-11	B	1	200
Feb-11	A	1	250
Mar-11	A	2	200
Apr-11	A	1	350
Mar-11	B	1	0
Apr-11	B	1	0
May-11	A	1	0

In this example, supplier A last month is is Apr/2011 and supplier B last month is Feb/2011

I really appreciate if somebody can give a support

Thanks in advance,

Leandro Duarte

[#2 · Accepted solution]

Hi Leandro

I understand you want the latest month where there was a value greater than 0, for each supplier, in the script?

My way to solve this would be to do a resident load, grouped by supplier that takes the maximum month where the value > 0, then join it onto the original table with a "dummy" field that acts as a flag.

ie:

left join (supp_tab)
load max(Month) as Month,Suppler, 1 as latest_flag resident supp_tab where Value>0 group by Supplier;

This would result in the same table, just with an extra field "latest_flag" which marks those months as being the latest.

Regards,

Erica