Dynamic column values in edit script

[kkkumar82]

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

[sunny_talwar · Accepted solution]

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);

[kkkumar82]

Super, Your are awesome Sunny