Un-concatenating row data into column headers with boolean true false

[SideOfRanch]

I found some threads doing similar stuff but everything I can find doesn't seem to work for me.

I have a dataset similar to this:

ID	MARKET	Other data
1	GA	123123
2	TN	41642
3	TN;GA	u76575
4	AL;TN;NC;ND;TX	3454

Trying to convert to

ID	Market	AL	GA	NC	ND	TN	TX
1	GA	0	1	0	0	0	0
2	TN	0	0	0	0	1	0
3	TN;GA	0	1	0	0	1	0
4	AL;TN;NC;ND;TX	1	1	1	1	1	1

I think it's some combination of creating a lookup table by converting the market's to rows and then doing a crosstab to create columns and generate result, but cannot figure it out.

[Maskell_Rascal]

Hi @SideOfRanch 

Here is how you can do it. 

I'm Parsing your Market field down to get a list of headers to use. Then Crosstab back with some Formula tools in there to get the desired values. Finally, I just Join it back to the original data to get the output. 

Let me know if this works for you. 

Cheers!

Phil

[atcodedog05]

Hi @SideOfRanch 

@Maskell_Rascal's has already nailed it with his approach. It should work for your scenario 🙂

Here is my take on it. Core logic is almost similar.

Workflow:

1. Using text to column to split market codes to rows

2. Using formula tool to add column count and set value as 1.

3. Using crosstab tool with key ID and Other data and market as name and count as value. You can pass all the main data columns as key and they will remain intact.

4. Using data cleanse tool to fill with 0 for null.

Hope this helps : )