Assigning unique numbers to nonidentical rows based on conditions & variance in diff rows

[nfoong]

Hi all,

This is quite a complicated ask in my opinion but I have 2 questions that I would like regarding the sample of the data below. The original dataset contains thousands of rows hence it is making it very difficult to make sense of it.

Ask #1: I need to assign a unique contract # as per below to thousands of rows and I need a way of saying if the SWIFT_ID and WEEKLY_COST is the same, assign the same unique number. If it is not the same, assign a unique number. An example of how it should look like is as below.

Ask #2: For the same SWIFT_ID but different Unique Contract # (eg: 4617 below with Unique Contract #1 & #2), calculate the difference between the weekly cost (£626.19 - £101.40) - this is so that I can identify the highest cost differentials between the different contracts. The next step would be to extract the top 10 contracts with the highest increase from the lowest value to the highest value in weekly cost.

SWIFT_ID	Unique Contract #	COST_START_DATE	COST_END_DATE	WEEKLY_COST
4617	1	08/04/2013	30/12/2015	£626.19
4617	2	07/04/2014	30/12/2015	£101.40
4639	3	08/04/2013	15/11/2015	£546.00
4639	4	07/04/2014	15/11/2015	£120.80
4639	4	16/11/2015		£120.80
5940	5	29/04/2016	22/09/2016	£1,950.00
5940	6	23/09/2016	23/09/2016	£0.00
6445	7	17/07/2014	28/01/2016	£1,100.00
6445	7	28/01/2016	26/02/2016	£1,100.00
6445	8	28/01/2016	27/04/2016	£1,824.64
6463	9	01/04/2013		£92.34
6463	10	08/04/2013		£1,131.44

Please let me know if you have any questions and thank you so much for your help!

Regards,

Nicholas

[jdunkerley79 · Accepted solution]

For 1:

I would do this using the Unique tool to create the unique set and then use a multi row formula grouped by SWIFT ID to assign a contact number.

Finally join back to the input and you should be done.

For 2:

You can use a multi row formula to create the differences then a summarise to get the max. You will need to convert weekly cost to a number,

Have put together a sample

[DataBlender]

For 1: I would suggest using the 'unique value' option of the Tile tool - you can accomplish this within one tool by selecting SWIFT ID and WEEKLY cost as your conditions. Note that two fields are created. Tile number is the assigned tile of the record. Tile sequence number is the record number of the record's position within the Tile.

For 2: Refer to @jdunkerley79's response

On a separate note, @jdunkerley79, I saw in your workflow that you have a dynamic rename as I presume you copied and pasted OP's sample data into the text input tool? If you place the cursor in the 'Field1' header and then press the paste button (rather than ctrl + v), then the table will be correctly formatted.

Apologies if that comes across condescending, just trying to help save some time :)

[jdunkerley79]

@DataBlender nice tip - will make it quicker to reply :)

[nfoong]

Thank you!

I think I've pretty much solved it thanks to your solution but can you please tell me what this means in the formula:

[Row-1:ContractID]+1

[jdunkerley79]

It is being used to create a unique id within the SWIFT_ID for each payment. It adds one to the previous rows value for ContractID.

This is the same as a RecordID tool but allows resetting within each group (SWIFT_ID).