Hello - I'm new to this community so please forgive me if i'm posting this in the wrong place.
i'm trying to analyze data below (only first few rows of millions of rows). What I'm trying to achieve here is if the account number, item number , contract type are same and the price is different I want to rename one of the rows in the contract type as CONTRACT1
ACCOUNT_NUMBER | ITEM NUMBER | Price (USD) | Contract Type |
100003 | 55262 | 12.34 | CONTRACT |
100003 | 55262 | 10.45 | CONTRACT |
100003 | 33623 | 11.51 | CONTRACT |
100003 | 33623 | 9.53 | CONTRACT |
100003 | 99526 | 14.58 | CONTRACT |
100003 | 99526 | 12.03 | CONTRACT |
100003 | 99528 | 18.12 | CONTRACT |
100003 | 99528 | 15.01 | CONTRACT |
100003 | 100105 | 15.02 | CONTRACT |
100003 | 100105 | 13.24 | CONTRACT |
100003 | 100105 | 13.24 | NON CONTRACT |
100003 | 100109 | 15.7 | CONTRACT |
This is how I want my data set to look like
ACCOUNT_NUMBER | ITEM NUMBER | Price (USD) | Contract Type |
100003 | 55262 | 12.34 | CONTRACT |
100003 | 55262 | 10.45 | CONTRACT1 |
Any help would be appreciated.
Solved! Go to Solution.
I've split the process into couple of steps so it's easier to understand
Step1 - Generate a sequence number (I call it OrderNo) for number of occurrences where Account_Number, Item Number and Contract Type are the same starting from 0.
I use the Multi-Row Formula in the below configuration to achieve this.
Group by the three values you'd like to be the same. The formula will restart for every new "group".
If IsEmpty([Row-1:OrderNo]) THEN 0
ELSE [Row-1:OrderNo]+1
ENDIF
Step2 - Use the Formula tool to create a new Contract type column by concatenating the original Contract type and OrderNo fields if the OrderNo is not 0.
IF [OrderNo] = 0 THEN [Contract Type]
ELSE [Contract Type]+ToSTring([OrderNo])
ENDIF
Example attached.
@bilal0310 - Should be doable with a multi-row tool and grouping based on account number, item number, and contract type. Can essentially create a grouped RecordID that you can add to your contract type column.
Attaching a quick sample.
You are a life saver! thank you so much !!