Normalize values on load by referencing specific record

[#1]

Hi,

I got some statistics info that show market share starting 2000 until 2010. I read the table like this

LOAD

Year as STAT_year,
amount as STAT_amount,
amount / amount[0] as STAT_factor | I know this line doesn't work!
RESIDENT STAT2010;

I want to "normalize" the amount values so that they become a factor or fraction of the amount in the first recordset that was read.

2000, 100, 1
2001, 110, 1.1
2002, 90, 0.9
a.s.o.

How can I do this? How can I reference the amount value of the first recordset?

Best regards,

Georg

[Miguel_Angel_Baeyens]

Hello Georg,

I understand you want to achieve this in the load script. You can use rangesum, peek or lookup to get something like

Normalized: Year as STAT_year, Amount as STAT_amount, Amount / Peek(Amount, 0) as FactorRESIDENT STAT2010;

Hope that helps.