Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
m_perreault
Creator III
Creator III

Creating a Counter Field by a Dimension

Hi All,


I have the below Data

IDSubID
A2343242
A6456456
B7896957
C2432645
C4764576
C3454352

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

IDSubIDID-Counter
A2343242A - 1
A6456456A - 2
B7896957B - 1
C2432645C - 1
C4764576C - 2
C3454352C - 3

So it would be essentially

ID & Autonumber(SubID)

by the number of SubID's for ID

Does anyone have any ideas?


Thanks,

Mark

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

5 Replies
andrey_krylov
Specialist
Specialist

Try this

LOAD *,

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

Resident YouTable

Order By ID;

m_perreault
Creator III
Creator III
Author

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
Specialist
Specialist

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

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
Specialist
Specialist

Hi, Sunny. Yes, I was in process)