community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

Text to columns then group by returned columns

Highlighted
Asteroid

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 IDDIFFERENCES
123456454654DUMMY12313212SECTOR_OF_REPT_CPTY;CPTY_SIDE;PRODUCT_ID;DELIVARABLE_CCY;VENUE_OF_EX;SETTLEMENT_DATE;AGREE_VERSION;FREQUENCY_OF_PY;PRODUCT_ID_TYPE;
123456454654DUMMY12313213AMT_OF_REPT_CPTY;CPTY_SIDE;PRODUCT_ID;DELIVARABLE_CCY;VENUE_OF_EX;SETTLEMENT_DATE;AGREE_VERSION;FREQUENCY_OF_PY;PRODUCT_ID_TYPE;
123456454654DUMMY12313214SCT_OF_REPT_CPTY;DELIVARABLE_CCY;SETTLEMENT_DATE;AGREE_VERSION;CL_OBLIGATION;FREQUENCY_OF_PY;
123456454654DUMMY12313215SECTOR_OF_REPT_CPTY;DELIVARABLE_CCY;SETTLEMENT_DATE;AGREE_VERSION;CL_OBLIGATION;FREQUENCY_OF_PY;FLING_RT_L
123456454654DUMMY12313216NOTI_CCY_2;EX_TIMESTAMP;SETTLEMENT_DATE;CONF_TIMESTAMP;CL_OBLIGATION;FREQUENCY_OF_PY;
123456454654DUMMY12313217NOTIONAL_CCY_2;EX_TIMESTAMP;SETTLEMENT_DATE;CONF_TIMESTAMP;CL_OBLIGATION;FREQUENCY_OF_PY;
123456454654DUMMY12313218EX_TIMESTAMP;SETTLEMENT_DATE;CONF_TIMESTAMP;CL_OBLIGATION;FREQUENCY_OF_PY;
123456454654DUMMY12313219EX_TIMESTAMP;SETTLEMENT_DATE;CONF_TIMESTAMP;CL_OBLIGATION;FREQUENCY_OF_PY;
123456454654DUMMY12313220SECTOR_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.

 

FieldCount of Field
AGREE_VERSION5
CL_OBLIGATION6
CONF_TIMESTAMP4
CPTY_SIDE3
DELIVARABLE_CCY5
EX_TIMESTAMP2
FLING_RT_L1
FREQUENCY_OF_PY9
NOTIONAL1
NOTIONAL_CCY_11
NOTIONAL_CCY_21
PRICE1
PRICE_NOTATION1
PRODUCT_ID2
PRODUCT_ID_TYPE2
SETTLEMENT_DATE9
VENUE_OF_EX2

 

Hope this makes sense as it is not easy to explain.

 

Any help appreciated.

 

Thanks!

 

Asteroid

Is this what you are looking for?

 

capture.PNG

 

Capture2.PNG

 

 

Capture3.PNG

 

Capture4.PNG

Asteroid

Wow awesome, exactly what I needed!

 

Thank you so much!

 

Labels