Update the column with incremental value

[alt_tush]

Hi,

I have below dataset.

Account	SECURITY	STAGE	FBE
S0001	SEC9954	S1	F1
S0002	SEC000399	S1	F1
S0002	SEC000399	S1	F1
S0002	SEC000399	S1	F1
S0002	SEC000399	S1	F2
S0002	SEC000399	S2	F1
S0003	SEC12345	S1	F1
S0003	SEC12345	S1	F1
S0003	SEC12345	S1	F1
S0004	SEC4000	S4	F4
S0004	SEC4000	S4	F4
S0005	SEC5000	S5	F5
S0005	SEC5001	S5	F5
S0005	SEC5001	S5	F6

My Expected output is MasterId column in below output.

Account	SECURITY	STAGE	FBE	MaserId
S0001	SEC9954	S1	F1	SEC9954_2
S0002	SEC000399	S1	F1	SEC000399_2
S0002	SEC000399	S1	F1	SEC000399_2
S0002	SEC000399	S1	F1	SEC000399_2
S0002	SEC000399	S1	F2	SEC000399_3
S0002	SEC000399	S2	F1	SEC000399_4
S0003	SEC12345	S1	F1	SEC12345_2
S0003	SEC12345	S1	F1	SEC12345_2
S0003	SEC12345	S1	F1	SEC12345_2
S0004	SEC4000	S4	F4	SEC4000_2
S0004	SEC4000	S4	F4	SEC4000_2
S0005	SEC5000	S5	F5	SEC5000_2
S0005	SEC5001	S5	F5	SEC5001_2
S0005	SEC5001	S5	F6	SEC5001_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 

[ShankerV · Accepted solution]

Hi @alt_tush 

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

Many thanks

Shanker V

[alt_tush]

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 · Accepted solution]

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.

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]

Hi @alt_tush 

Please find the output with the help of Tile tool.

Many thanks

Shanker V

[binuacs]

@alt_tush Similar to @ShankerV solution