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

Converting data format

PuffinPanic
9 - Comet
9 - Comet

Hi All,

 

I've been banging my head against this for the last hour or so, and I just can't figure it out, so I'm hoping that someone can help.

 

I have a set of data that is in this format:

Job 1 categoryJob 1 descriptionJob 2 categoryJob 2 descriptionJob 3 categoryJob 3 description
category 1Job 1 cat 1 description 1category 1Job 2 cat 1 description 1category 1Job 3 cat 1 description 1
category 1Job 1 cat 1 description 2category 1Job 2 cat 1 description 2category 1Job 3 cat 1 description 2
category 1Job 1 cat 1 description 3category 2Job 2 cat 2 description 1category 1Job 3 cat 1 description 3
category 2Job 1 cat 2 description 1category 2Job 2 cat 2 description 2category 1Job 3 cat 1 description 4
category 2Job 1 cat 2 description 2category 3Job 2 cat 3 description 1category 2Job 3 cat 2 description 1
category 2Job 1 cat 2 description 3category 3Job 2 cat 3 description 2category 2Job 3 cat 2 description 2
category 3Job 1 cat 3 description 1category 3Job 2 cat 3 description 3category 2Job 3 cat 2 description 3
category 3Job 1 cat 3 description 2  category 3Job 3 cat 3 description 1
category 3Job 1 cat 3 description 3  category 3Job 3 cat 3 description 2

 

but I need it to look like this:

CategoryJob 1Job 2Job 3
category 1Job 1 cat 1 description 1Job 2 cat 1 description 1Job 3 cat 1 description 1
category 1Job 1 cat 1 description 2Job 2 cat 1 description 2Job 3 cat 1 description 2
category 1Job 1 cat 1 description 3 Job 3 cat 1 description 3
category 1  Job 3 cat 1 description 4
category 2Job 1 cat 2 description 1Job 2 cat 2 description 1Job 3 cat 2 description 1
category 2Job 1 cat 2 description 2Job 2 cat 2 description 2Job 3 cat 2 description 2
category 2Job 1 cat 2 description 3 Job 3 cat 2 description 3
category 3Job 1 cat 3 description 1Job 2 cat 3 description 1 
category 3Job 1 cat 3 description 2Job 2 cat 3 description 2Job 3 cat 3 description 1
category 3Job 1 cat 3 description 3Job 2 cat 3 description 3Job 3 cat 3 description 2

 

It's not a straightforward as just removing the category columns, because some have more descriptions for the same category as others. I have a large number of jobs and their associated descriptions so I didn't really want to do this manually - although it's taken me so long I might have been better off just doing a copy & paste job now, but I don't like to be beaten.

 

I'm hoping it's something simple and it's just me not spotting the obvious solution.

 

Any help would be appreciated.

 

Thanks

PuffinPanic

5 REPLIES 5
ChrisTX
15 - Aurora

See if the attached workflow gives you the correct output.  Not sure if the highlighted cells are correct in your example, or in the workflow.

 

When you mentioned "I have a large number of jobs", I don't know if that means you have more columns besides Job 1, Job 2, Job 3.  If you do have many columns, you could convert the middle section of the workflow to a batch macro.

 

ChrisTX_0-1637254959518.png

 

 

Chris

Ben_H
11 - Bolide

Hi @PuffinPanic,

 

Chris got there before me but here's an alternate method, that uses the transpose tool.

Ben_H_0-1637256035516.png

I got the same result as Chris - slightly different order than your target example.

 

Ben_H_1-1637256157912.png

Regards,

 

Ben

PuffinPanic
9 - Comet
9 - Comet

Hi Chris,

 

Sorry, that wasn't as clear written down as in my head. 

 

I meant that this is a subset of the jobs, so job1, job2, etc, could go on to job28 or more.

 

I haven't designed any macros myself before, although I have used a couple that other people have posted on the forum, so that will be interesting.

 

Also, the formatting of your answer is correct, it was my example where it wasn't lined up properly - the perils of copy and paste!

 

Thanks for the response, I will have a go today to see if I can get it working. 

PuffinPanic
9 - Comet
9 - Comet

Thanks Ben,

 

I'll try it out today.

PuffinPanic
9 - Comet
9 - Comet

Hi,

 

Ben, that solution worked perfectly. I've tweaked it a bit to make it work with my actual data, but it definitely does the job. 

 

Thank you both for your helpful suggestions. 

 

Kind regards

PuffinPanic

Labels