Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the following data
Data:
load * Inline [
ID,Year,Quarter,Amount
1,2012,Q1,100
1,2012,Q2,200
1,2012,Q3,300
1,2012,Q4,400
1,2013,Q1,500
1,2013,Q2,600
1,2013,Q3,700
1,2013,Q4,800
1,2014,Q1,500
1,2014,Q2,600
1,2014,Q3,700
1,2014,Q4,800
];
if you observe there are 3 distinct values for the column Year, after this what I want is I want to have another table with column 'Dim' which should have three values 1,2,3 because the count is 3 for the number of distinct years.
I started like this but couldn't figure out how to add three values to the new table column Dim with values 1, 2 and 3.
Counts:
Load Count(Distinct Year) as Count
Resident Date;
Let vcount = Peek('Count');
For i = 1 to $(vcount)
......
....
next
Any suggestion for the code in the loop
Thanks
Kiran Kumar
May be this?
load * Inline [
ID,Year,Quarter,Amount
1,2012,Q1,100
1,2012,Q2,200
1,2012,Q3,300
1,2012,Q4,400
1,2013,Q1,500
1,2013,Q2,600
1,2013,Q3,700
1,2013,Q4,800
1,2014,Q1,500
1,2014,Q2,600
1,2014,Q3,700
1,2014,Q4,800
];
LET vCount = FieldValueCount('Year');
Table:
LOAD RowNo() as Dim
AutoGenerate $(vCount);
May be this?
load * Inline [
ID,Year,Quarter,Amount
1,2012,Q1,100
1,2012,Q2,200
1,2012,Q3,300
1,2012,Q4,400
1,2013,Q1,500
1,2013,Q2,600
1,2013,Q3,700
1,2013,Q4,800
1,2014,Q1,500
1,2014,Q2,600
1,2014,Q3,700
1,2014,Q4,800
];
LET vCount = FieldValueCount('Year');
Table:
LOAD RowNo() as Dim
AutoGenerate $(vCount);
Super, Your are awesome Sunny