Creating a Counter Field by a Dimension

[m_perreault]

Hi All,

I have the below Data

ID	SubID
A	2343242
A	6456456
B	7896957
C	2432645
C	4764576
C	3454352

and id like to create a field in the script that would give me the below table

ID	SubID	ID-Counter
A	2343242	A - 1
A	6456456	A - 2
B	7896957	B - 1
C	2432645	C - 1
C	4764576	C - 2
C	3454352	C - 3

So it would be essentially

ID & Autonumber(SubID)

by the number of SubID's for ID

Does anyone have any ideas?

Thanks,

Mark

[andrey_krylov]

Try this

LOAD *,

       If(ID = Previous(ID), RangeSum(Peek('ID-Counter'), 1), 1) as [ID-Counter]

Resident YouTable

Order By ID;

[m_perreault]

Thanks Andrey,

to get my desired result I have

Load *,

ID & '-' & [ID-Counter] as ID2

;

Load

      If(ID = Previous(ID), RangeSum(Peek('ID-Counter'), 1), 1) as [ID-Counter]

Resident YouTable

Order By ID;

Do you know if there is a way to accomplish this without using a preceding load?

Thanks,
Mark

[andrey_krylov]

Hi, Mark, of course. It looks like i've missed something, no preceding load is needed.

Load *,

    ID & '-' & If(ID = Previous(ID), RangeSum(SubField(Peek('ID-Counter'), '-', 2), 1), 1) as [ID-Counter]

Resident YouTable

Order By ID;

[sunny_talwar · Accepted solution]

I think this might not work because now the ID Counter is a string and you cannot really add 1 to a string... may be this

LOAD

      If(ID = Previous(ID), RangeSum(Peek('ID-Counter'), 1), 1) as [ID-Counter],

      ID &  '-' & If(ID = Previous(ID), RangeSum(Peek('ID-Counter'), 1), 1) as ID2

Resident YouTable

Order By ID;

[andrey_krylov]

Hi, Sunny. Yes, I was in process)