Hi,
I am working with the following script:
[Table A]:
LOAD
Year
as YEAR,
Indicator_Name
AS Series,
Values
AS Fact
FROM
[Test.csv]
The Table looks a bit like this:
The Table looks a bit like this:
Year Indicator_Name Values
2000 IndicA 50
2001 IndicA 60
2002 IndicA 70
2000 IndicB 50
2001 IndicB 60
2002 IndicB 70
2000 IndicC 50
2001 IndicC 60
2002 IndicC 70
What I would like to achieve, is to add a column with a Indicator_key, like this:
Year Indicator_Name Values Indicator_Key
2000 IndicA 50 1
2001 IndicA 60 1
2002 IndicA 70 1
2000 IndicB 50 2
2001 IndicB 60 2
2002 IndicB 70 2
2000 IndicC 50 3
2001 IndicC 60 3
2002 IndicC 70 3
Now the tricky part is, I don't want to put the explicit texts of the Indicator_Name field in IF statements in my script.
Rather, I am looking for a mechanism that will assing the Key 1 to the first Indicator_Name occurence, then, give the same Key to all the subsequent Name's that are the same as the first one, and add +1 to the Key for each new Name. I would do this on a list pre-sorted by Indicator name.
What are your thoughts on doing this?
Thanks!
