removing duplicate rows and converting duplicate amount into zero

[Tid14]

 

 

Hi, I am working on a workflow where I am using the above attached input, In the given data first 7 columns are same and then from purchase to Surplus it differs.

I want output wherein the duplicate

data having same rows in first seven columns gets removed and it shows up only first row and then in the last surplus amount column the amount in the first row shows up as 9265.62 and then in the remaining 6 rows having the same amount it covert into zero as i am attaching in the image of output i want to get.

Any help on this please?

Thanks in Advance

[shancmiralles]

Hi @Tid14 ,

1) SELECT TOOL  TO CHANGE THE DATA TYPE OF THE FIRST 7 COLUMNS TO V_WSTRING

2) CONNECT A FORMULA TOOL  AND CREATE A NEW COLUMN ( I NAMED MINE "1-7 COLUMN") AND CONCAT WITH THE FF FORMULA : " [Supplier Name]+[plant]+[Material]+[Material Name]+[Material Group Description]+[MRP]+[Surplus]"

3) USE A UNIQUE TOOL  AND SELECT 1-7 COLUMNS FOR THE CONFIGURATION

4) ON THE "D" (DUPLICATE) OUTPUT ANCHOR OF THE UNIQUE (3) TOOL CONNECT A DATA CLEANSING TOOL  AND SELECT FIELDS 1-7 AND CHECK ALL CHOICES UNDER THE "REMOVE UNWANTED CHARACTERS". (THIS WILL REMOVE ALL DATA FRM 1-7 COLUMNS)

5) ON THE "U" (UNIQUE) OUTPUT ANCHOR OF THE UNIQUE TOOL (3) CONNECT A UNION TOOL () 

6) YOUR DATA CLEANSING TOOL  FROM #4, CONNECT THAT TO THE #5 UNION TOOL AS WELL. 

7)   TO MAKE SURE THEY'LL BE ALIGNED ACCORDING TO THE 1-7 DAT COLUMN USE  A SORT TOOL () TO YOUR 1-7 COLUMN" (#2)

8) JUST USE A SELECT TOOL TO REMOVE THE CREATED COLUMN AND A FORMULA TOOL FOR THE TOTAL, SURPLUS VAL, ETC. 

HOPE THIS HELPS! :)