Text to columns then group by returned columns

[collinjd]

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!

[rarmstrong · Accepted solution]

Is this what you are looking for?

[collinjd]

Wow awesome, exactly what I needed!

Thank you so much!