topic Re: Creating a Counter Field by a Dimension in App Development

Creating a Counter Field by a Dimension

m_perreault — Fri, 24 Aug 2018 21:07:04 GMT

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

Re: Creating a Counter Field by a Dimension

andrey_krylov — Sat, 25 Aug 2018 03:49:14 GMT

Try this

LOAD *,

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

Resident YouTable

Order By ID;

Re: Creating a Counter Field by a Dimension

m_perreault — Mon, 27 Aug 2018 13:51:12 GMT

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

Re: Creating a Counter Field by a Dimension

andrey_krylov — Mon, 27 Aug 2018 17:58:19 GMT

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;

Re: Creating a Counter Field by a Dimension

sunny_talwar — Mon, 27 Aug 2018 18:01:46 GMT

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;

Re: Creating a Counter Field by a Dimension

andrey_krylov — Mon, 27 Aug 2018 18:08:07 GMT

Hi, Sunny. Yes, I was in process)