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

Question

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:

IDMARKETOther data1GA1231232TN416423TN;GAu765754AL;TN;NC;ND;TX3454

Trying to convert to

IDMarketALGANCNDTNTX1GA0100002TN0000103TN;GA0100104AL;TN;NC;ND;TX111111

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.

atcodedog05 · Answer

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 : )

flags.yxmd

Maskell_Rascal · Answer

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

Parse-Crosstab.yxmd