Split one column into multiple columns

[Shannon11113]

Hi, I am trying to split the column JSON_Name 23 into individual columns such that one column would show only ServiceNo, another column would only show Operator and then the next column would show NextBus etc.  Is there a way to do this without any code? 

[DavidSkaife]

Hi @Shannon11113 

Yes, the Crosstab tool will do this for you:

It will only allow you to use one column for the headers, so you'd need to join the data back to your main dataset after the transformation. You'll also need to group the data by a field to stop the aggregation. Example tool config that i used is below:

[Shannon11113]

Hi @DavidSkaife , thank you for replying to me. I am slightly confused about your answer, but i've tried what you did. However, i'm not sure why the ServiceNo and Operator is not showing... 

[DavidSkaife]

Hi @Shannon11113 

You won't need to drop in a new RecordID tool if you already have one in the data, or another identifier. This is used to ensure you're data doesn't aggregate.

Only thing i can think as to why those fields are not appearing is the JSON_ValueString2 field for those values is null, but that shouldn't stop them coming through..

Are you able to share a subset of the data for me to look at?

[Shannon11113]

HI @DavidSkaife sorry I am not able to share the data with you. I have managed to make the results much more appealing, but I am still stuck at trying to make operator and serviceNo that is in the column JSON_Name 23. Is there any other way to make it work? 

[OllieClarke]

@Shannon11113 

depending on your use-case, you could use a multi-row formula creating an int32 field called row, grouped by Json_Name23 with this formula

[row-1:row]+1

Grouping by this can group content across rows like below:

Otherwise @DavidSkaife has you covered. 

I think you might be filtering out the operator and serviceCode in the first screenshot you posted above - it's hard to tell from the second (although you probably don't want to group by JSON_Name23 as well as using it in your headers)

Hope that helps,

Ollie

[Shannon11113]

Hi @OllieClarke thank you so much for your help! I finally managed to get the serviceNo and Operator out, but it does not match with the rest of the columns... I have tried using the data cleaning tool but it did not give me the desired result. Do you know how I can solve this? 

[OllieClarke]

Hi @Shannon11113 it would be much easier to help if you could share some data (could well be fake) as it looks like now, and how you want it to look - otherwise there's a fair amount of guesswork going on...