Hi There,
I need some assistance in building a workflow.
I have a file that is about 150 columns and thousands of rows but 1 of the columns in the file "Differences", has multiple field names separated by semicolon which tells us the incorrect fields out of the 150 columns, this is different for each row as it is dependant of a reference ID and fields that are incorrect per ID. The below table is an example of how the fields looks for 1 particular reference. If I do a text to column on the semicolon this shows that we are breaking on 9 fields. That is the easy part.
| SECTOR_OF_REPT_CPTY;CPTY_SIDE;PRODUCT_ID;DELIVARABLE_CCY;VENUE_OF_EX;SETTLEMENT_DATE;AGREE_VERSION;FREQUENCY_OF_PY;PRODUCT_ID_TYPE; |
The next part I am stuck, as each reference has a different number of fields they are breaking on (can range from 1-150 fields). I need a simple sum total of each field name that is breaking on for each reference. I have attached an example table with dummy data with 9 references as an example and the column that contains the differences field.
| Reference ID | DIFFERENCES |
| 123456454654DUMMY12313212 | SECTOR_OF_REPT_CPTY;CPTY_SIDE;PRODUCT_ID;DELIVARABLE_CCY;VENUE_OF_EX;SETTLEMENT_DATE;AGREE_VERSION;FREQUENCY_OF_PY;PRODUCT_ID_TYPE; |
| 123456454654DUMMY12313213 | AMT_OF_REPT_CPTY;CPTY_SIDE;PRODUCT_ID;DELIVARABLE_CCY;VENUE_OF_EX;SETTLEMENT_DATE;AGREE_VERSION;FREQUENCY_OF_PY;PRODUCT_ID_TYPE; |
| 123456454654DUMMY12313214 | SCT_OF_REPT_CPTY;DELIVARABLE_CCY;SETTLEMENT_DATE;AGREE_VERSION;CL_OBLIGATION;FREQUENCY_OF_PY; |
| 123456454654DUMMY12313215 | SECTOR_OF_REPT_CPTY;DELIVARABLE_CCY;SETTLEMENT_DATE;AGREE_VERSION;CL_OBLIGATION;FREQUENCY_OF_PY;FLING_RT_L |
| 123456454654DUMMY12313216 | NOTI_CCY_2;EX_TIMESTAMP;SETTLEMENT_DATE;CONF_TIMESTAMP;CL_OBLIGATION;FREQUENCY_OF_PY; |
| 123456454654DUMMY12313217 | NOTIONAL_CCY_2;EX_TIMESTAMP;SETTLEMENT_DATE;CONF_TIMESTAMP;CL_OBLIGATION;FREQUENCY_OF_PY; |
| 123456454654DUMMY12313218 | EX_TIMESTAMP;SETTLEMENT_DATE;CONF_TIMESTAMP;CL_OBLIGATION;FREQUENCY_OF_PY; |
| 123456454654DUMMY12313219 | EX_TIMESTAMP;SETTLEMENT_DATE;CONF_TIMESTAMP;CL_OBLIGATION;FREQUENCY_OF_PY; |
| 123456454654DUMMY12313220 | SECTOR_OF_REPT_CPTY;CPTY_SIDE;DELIVARABLE_CCY;PRICE;PRICE_NOTATION;NOTIONAL;NOTIONAL_CCY_1;NOTIONAL_CCY_2;SETTLEMENT_DATE;AGREE_VERSION;FREQUENCY_OF_PY |
What I need from Alteryx is to provide me is a breakdown of all the fields within that differences column and sum how many times they are breaking, returning in the format of the table below.
| Field | Count of Field |
| AGREE_VERSION | 5 |
| CL_OBLIGATION | 6 |
| CONF_TIMESTAMP | 4 |
| CPTY_SIDE | 3 |
| DELIVARABLE_CCY | 5 |
| EX_TIMESTAMP | 2 |
| FLING_RT_L | 1 |
| FREQUENCY_OF_PY | 9 |
| NOTIONAL | 1 |
| NOTIONAL_CCY_1 | 1 |
| NOTIONAL_CCY_2 | 1 |
| PRICE | 1 |
| PRICE_NOTATION | 1 |
| PRODUCT_ID | 2 |
| PRODUCT_ID_TYPE | 2 |
| SETTLEMENT_DATE | 9 |
| VENUE_OF_EX | 2 |
Hope this makes sense as it is not easy to explain.
Any help appreciated.
Thanks!
