Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Split the record based on the delimiter

jmedidi
8 - Asteroid

I want to split the record as below. Appreciate the support.

 

 

Existing Record:

RecordWODistrictLeadTechService
1A00100, A00101North,southKhan, LopezAssess, Replace

 

Desired Result:

 

RecordWODistrictLeadTechService
1A00100NorthKhanAssess
2A00101southLopezReplace
8 REPLIES 8
Christina_H
14 - Magnetar

This works on the data provided

Kenda
16 - Nebula
16 - Nebula

Hi @jmedidi 

 

In this case, it may help to transpose the data first. This will allow you to then use just one Text to Columns tool (splitting to rows). I added a Formula tool to just trim this field that we split so there is no extra whitespace. I then used a Multi-Row Formula tool to edit the Record column so that we could then use a Cross Tab to get the records back on their own rows. 

 

Hope this helps!

 

 

 

Kenda_0-1627917149563.png

 

jmedidi
8 - Asteroid

Thanks for quick response. 

 

Will this work if i have entries more than 2 in the cell for eg., north, south, east, west and other entries remain same ?

atcodedog05
22 - Nova
22 - Nova

Hi @jmedidi 

 

The method should work for n number of splits also. You can try and check it out.

Kenda
16 - Nebula
16 - Nebula

Hello @jmedidi 

 

If you update the District cell in the Text Input in the workflow attached previously with no updates to the other fields, you will see that the output now contains 4 records as follows:

 

Kenda_0-1627918768164.png

 

jmedidi
8 - Asteroid

Hello Kenda,

 

Actually i have added column Record for illustration purpose only. If i remove the column Record , i am getting an error "Type Mismatch in operator + " at Mutil row formula.

 

Appreciate your support.

Kenda
16 - Nebula
16 - Nebula

Hi @jmedidi 

 

This is because the Multi-Row Formula tool is actually trying to update the Record field. If you want to get rid of it from your input, you could change the Multi-Row formula tool so that it creates a new field called Record instead (with the same formula as before). This field is necessary because the Cross Tab tool uses it to group the data and separate the rows correctly. 

 

Hope this helps!

atcodedog05
22 - Nova
22 - Nova

Hi @jmedidi 

 

Edit: same as @Kenda said.

 

Modify multi-row formula tool like below highlighted. It should work.

 

atcodedog05_0-1628175027551.png

 

Hope this helps : )

 

Labels