Transform / pivot / group data question

Hi everyone,

I've tried both the Cross Tab and Transpose tools in ways which I normally use them, and haven't had any luck.

Referring to the graphic below:

I get SOURCE_DATA from the reporting team. In this simplified example, you can see that there two CLASSES: FRUIT and VEGETABLE.

Within each CLASS, there are multiple ITEMS: APPLE and ORANGE; and CARROT, CAULIFLOWER, and LETTUCE

My goal (and this is where I'm looking for guidance) is to transform the SOURCE_DATA into the TRANSFOMED data: each ITEM from each CLASS gets paired with each other ITEM from each other CLASS.

Is this a one-tool job? Something which requires Alteryx gymnastics?

Thanks,

Nick

Developer

Accepted answers

All comments

Luke_C

Hi @Nicholas_White

Try something like this. You can achieve the 'transformed' view with an append fields tool, then use formulas to create you're final tool.

New Workflow2.yxmd

apathetichell

One quick addendum @Luke_C has assumed you have no duplicates in your data. If you have (potential) duplicates you'd want to hook up summarize tools in group-by mode for your fruit/vegetable fields before each anchor of your append data tool.

Nicholas_White

@Luke_C ,

Thank you very much for your reply.

I had come up with a solution using the Append Tool like your example, but that solution is fixed by however many append tools I insert into the workflow.

In hindsight, I should definitely have mentioned in my original post that the SOURCE_DATA could contain potentially dozens of CLASSES, and hundreds of ITEMS.

As an example, I added two more CLASSES (BAKERY and MEAT), with several more ITEMS inside them. The real SOURCE_DATA file would contain lots more information in it.

I think what I'm after is a way to dynamically break apart the SOURCE_DATA by CLASS, then do a Cartesian Join with all the ITEMS.

Thanks,

Nick

Nicholas_White

Hi everyone,

Ok, so one of the reasons I love this forum so much is because by communicating with others, you can sometimes answer your own questions by people presenting the same information you already had in a different manner.

When staring at @Luke_C 's proposed solution, it hit me that I needed to do a better search in the Community to see if my question had been answered already.

Fortunately, it had!

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Append-based-on-Column/td-p/668939

@PhilipMannering proposed a Python solution which uses the PRODUCT() function from the ITERTOOLS library. I'll need to dig into it to fully understand how it works, but the outcome is exactly what I was looking for.

This:

Becomes this:

Thank you,

Nick

Luke_C

@Nicholas_White Love it!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703