Transforming Data

trI need to transform data from rows to columns.

So the group field is called PIN but then I have Lic #, Lic Type, Lic Date.

I need to include all lic fields on the same row as the pin. How can I accomplish this?

So like this:

Pin	Lic #	Lic type	Lic Date	Lic #	Lic type	Lic Date
100	1	Small	1/1/2023	4	Medium	4/1/2023
200	2	Small	2/1/2023	5	Medium	5/1/2023
300	3	Small	3/1/2023	6	Medium	6/1/2023

Transformation

Accepted answers

All comments

jrgo

I'm thinking the Crosstab tool, but column headers are required to be unique so they won't look like the way you pictured in your table.

If you can also share what your source/start data looks like, we may be able to provide a more detailed solution.

ed_hayter

We can use a combo of transpose and crosstab to do this once we make an identifier for new rows.

CowannBell.yxmd

cowannbell

I want all lice on the same row as the pin. Your example has it on different rows.

ed_hayter

Apologies - I see now that is your desired output. Can you quickly demonstrate what it would look like coming in

cowannbell

Sure, actually exactly what you have in our output, without the record id. So I need the reverse.

ed_hayter

You can group by the pin and concatenate the other fields. This does not get exactly to your desired output of having the information in separate fields. To get that I used some brute force with text-to-columns splitting on the ",".

This works fine when there are only ever two of each but I'd need to spend more time thinking up a dynamic solution to split based on the number of records per pin. Suppose you could increase the split to a larger number than the maximum and then remove null columns. Potential problem is that you are making the data very wide and not as performant.

I'll leave the brute force one here and then give a smarter solution some thought.

The second workflow is one that expands the table and removes null columns

CowannBell.yxmd

CowannBell_Expand then contract.yxmd

cowannbell

I think I might have confused you.

Maybe this will help.

How data is


Pin	Lic #	Lic Type	Lic Date
100	1	Small	1/1/2023
100	2	Medium	1/1/2023
100	3	Large	3/1/2023
200	5	Small	4/1/2023
200	7	Medium	5/1/2023
200	8	Large	6/1/2023
300	10	Small	2/1/2023
300	14	Medium	7/1/2023
300	23	Large	8/1/2023


How I want it


Pin	Lic #	Lic Type	Lic Date	Lic #	Lic Type	Lic Date	Lic #	Lic Type	Lic Date
100	1	Small	1/1/2023	2	Medium	1/1/2023	3	Large	3/1/2023
200	5	Small	4/1/2023	7	Medium	5/1/2023	8	Large	6/1/2023
300	10	Small	2/1/2023	14	Medium	7/1/2023	23	Large	8/1/2023

cowannbell

I think I might have confused you.

Hopefully the attached will help.

Capture.PNG

ed_hayter

The above Workflow would achieve that output - although you cannot have column headers with identical names - hence the suffix (LIC_1, LIC_2 ETC.). But the logic of bringing all that information onto the same row for each pin and then splitting on the list delimiter is likely the best way to achieve this.

The second part of my comment was more rambling about thinking of more dynamic ways of getting to the desired output (when pins have varying amount of LICs in them.)

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703