Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Update the column with incremental value

alt_tush
9 - Comet

Hi,

 

I have below dataset.

 

AccountSECURITYSTAGEFBE
S0001SEC9954S1F1
S0002SEC000399S1F1
S0002SEC000399S1F1
S0002SEC000399S1F1
S0002SEC000399S1F2
S0002SEC000399S2F1
S0003SEC12345S1F1
S0003SEC12345S1F1
S0003SEC12345S1F1
S0004SEC4000S4F4
S0004SEC4000S4F4
S0005SEC5000S5F5
S0005SEC5001S5F5
S0005SEC5001S5F6

 

 

My Expected output is MasterId column in below output.

 

AccountSECURITYSTAGEFBEMaserId
S0001SEC9954S1F1SEC9954_2
S0002SEC000399S1F1SEC000399_2
S0002SEC000399S1F1SEC000399_2
S0002SEC000399S1F1SEC000399_2
S0002SEC000399S1F2SEC000399_3
S0002SEC000399S2F1SEC000399_4
S0003SEC12345S1F1SEC12345_2
S0003SEC12345S1F1SEC12345_2
S0003SEC12345S1F1SEC12345_2
S0004SEC4000S4F4SEC4000_2
S0004SEC4000S4F4SEC4000_2
S0005SEC5000S5F5SEC5000_2
S0005SEC5001S5F5SEC5001_2
S0005SEC5001S5F6SEC5001_3

 

Logic is :- If account, security, stage and FBE column value is same then MasterId value should be Security_2 i.e in first row MasterId output should  be SEC9954_2

 

If Account & Security value is same but any other value from stage and FBE is different then masterId value should be incremental. See the row number 5 & 6 masterId value is SEC000399_3 & SEC000399_4 respectively.

 

Please note incremental value will be start with 2. Any duplicates entries where all the column values are same, masterId concate with "_2".

 

Thank you 

 

5 REPLIES 5
ShankerV
17 - Castor

Hi @alt_tush 

 

Please find the expected output via simple tools. No macro needed.

ShankerV_0-1676309727845.png

 

 

ShankerV_0-1676309681110.png

 

 

Many thanks

Shanker V

alt_tush
9 - Comet

Hi Shankar V,

 

Thank you so much for your quick reply.

 

The logic which you mentioned it works only my data is in sorting order. If i understand correctly multi row logic comparing the current row with previous row. If the present row is not for same security or account or data is not in sorting order then it might chances of incorrect masterid/output.

Also i have large volumne of data where there is checks on multiple columns like FBE stage etc. so sorting operation will take processing time.

 

Is there any other way to resolve my query like tile tool or some other tool.

 

Thank you for your help. Your solution also helpful :) 

ShankerV
17 - Castor

Hi @alt_tush 

 

Immediate solution via Sort only comes to mind, but please use the Record ID tool before sorting.

So that we can bring back the records in the order where input was with the help of it.

 

ShankerV_0-1676310976304.png

 

Note: Will try with other tools also, but not sure will it give the correct output in case of large data.

 

Many thanks

Shanker V

ShankerV
17 - Castor

Hi @alt_tush 

 

Please find the output with the help of Tile tool.

 

ShankerV_0-1676311618726.png

 

Many thanks

Shanker V

binuacs
20 - Arcturus

@alt_tush Similar to @ShankerV solution

binuacs_0-1676361028413.png

 

Labels