Join between row value/column header?

[jeremyblaney]

I have a dataset that needs to go into a shell, but I'm not sure how best to do so. Here's what my dataset and the shell looks like:

Dataset 1

Organization	Movement Type	Count
Org A	Type 1	11
Org A	Type 2	12
Org B	Type 1	16
Org B	Type 2	28
Org B	Type 3	24
Org C	Type 2	28

Shell

Organization	Type 1	Type 2	Type 3	Type 4	Type 5	Type 6	Type 7
Org A
Org B
Org C

You can see the movement types in the source dataset are columns in the shell.

Is there a way to put the value in the appropriate org (row)/movement type (column) without transposing the shell? In other words a IF Org = Org AND Movement Type = Column Name THEN Count?

I'd like to keep the shell as is because it's easier to see what's going on, create custom formulas (some of the type fields are calcs of the previous types), etc.

[mbarone]

Try the Crosstab tool.

[AndrewL · Accepted solution]

Hi @jeremyblaney,

Unfortunately there's no way to build an expression inside a Join tool to do that kind of conditional Join. That said, the easier way to do this, if you don't want to reformat your shell, would be to reformat the data set to conform to the shell.

You can do this very easily using the CrossTab tool. I've attached a simple sample showing the configuration, and included a step using the Data Cleansing tool to replace null values with 0s, which you may or may not want to do depending on your use case.

If this is not quite what you're looking for you may want to reach out to support@alteryx.com with your workflow and data (if you can share) so we can better assist.

Thanks,

Andrew

[AndrewL]

Take a look at my response to your original post here: https://community.alteryx.com/t5/Data-Preparation-Blending/Join-between-row-value-column-header/m-p/...

[JessicaS]

Hello all,

Two posts have been merged together on this question.

Thanks!