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

Join and Join Multiple tool rename problem

Williamcai
7 - Meteor

I have tried both Join and Join Multiple tool, and haven't find the best solution for my need yet.

 

I made an example to show what I am trying to achieve. I have a database looks like the following

 

Datespecvalue
1-Jula10
1-Julb20
1-Julc30
2-Julb40
2-Julc50
2-Juld

60

 

I am trying to compare the value difference between the two dates according to spec. Basically I want to create a table looks like the following:

Date1specvalue1date2value2
1-Jula10nullnull
1-Julb202-Jul40
1-Julc302-Jul50
nulldnull2-Jul60

 

Obviously I need a full outer join, and I will need to rename the columns except the spec. I have tried both the Join tool and multiple join tool. and they both have some limitation to achieve my desired result.

 

For the Join tool, it will only rename the columns for the J output, but not for L and R output, which makes the following union result look strange (I need to union the L, J, R output), it has misplaced date column in the end of the columns.

 

For the Join Multiple tool, it will either omit the left input or the right input when it can't find the match, which means my spec value is gone. 

 

attached is my workflow.

Capture.JPG

 

8 REPLIES 8
JulioMO
9 - Comet

Hi @Williamcai ,

 

In the Join Tool you have to select "Manually Configure Fields" instead of "Auto Config by name" and then manually configure the columns as you may require for your output: 

Config Window.JPG

 

You can also rename #1, #2, #3 so it is easier to identify which inputs your are working with. 

 

As for the Join Multiple, you will need to add a row with the spec "d" in your 1-jul data, so it does no omit any data:

Capture.JPG

 

Attach you will find the workflow. 

 

Regards, 

 

Thableaus
17 - Castor
17 - Castor

Hi @Williamcai 

 

You can use a formula tool and a select tool right after to get your input #2 spec to be your spec.

 

joinmulti.PNG

 

IF IsNull([spec]) THEN [Input_#2_spec]
ELSE [spec] endif

 

Then, use the select tool to get rid of the Input#2 field.

 

WF attached.

Cheers,

Williamcai
7 - Meteor

Thank you @JulioMO 

 

I thought about manually configuring fields, but I gave up. Because in reality, my spec is consisted of more than 20 columns, and value is about 16 columns. To manually align everything is simply too many manual clicks. Alteryx is really making this manual alignment super painful: it will not hold the remaining columns in place, thus in order to manually align them, I need to click basically 36 + 35 + 34 + ... + 1 times to align one input. The equal amount of clicks to align the other inputs. 

Williamcai
7 - Meteor

Thanks @Thableaus

 

This should work. I have to write a lot of if statements to manually correct the missing spec value, cause in reality I have 20 columns of spec.  

Right now, I am trying to use Join tool, followed by select tool to re-name the column names, then join. It's tedious.

 

I am curious to see if there are better solutions. 

Thableaus
17 - Castor
17 - Castor

@Williamcai 

 

Not the best scenario here but it could work:

 

try1.PNG

 

try2.PNG

 

- Use a Max formula (yes, in this case you'd need to mention all of your spec fields)

IF IsNull([spec]) THEN max([Input_#2_spec], [spec])
ELSE [spec] endif

 

- Use the Dynamic Select tool to get rid of the repeated fields. The repeated fields contain the word "Input", so you can use a contain function.

 

WF attached.

 

Cheers,

kgalbert
9 - Comet

Hi @Williamcai 

 

Are you really attached to the output you posted?  Would something like this work?  Much simpler to maintain.

 

Each Spec gets a row, the dates are broken out to their own columns, and the values are summed up.

 

Thanks,
Ken

JulioMO
9 - Comet

Hi @Williamcai 

 

In that case you could use a Select Tool in order to rearrange your Spec to column to be the first, as shown

 

Capture1.JPGCapture2.JPG

 

Then , in the Manually Configure Fields, you can select all the columns you want to rearrange and drag them all together with the two arrows on the right of the following pic:

Capture.JPG

 

You would then be able to rearrange all the columns in two steps. 

Williamcai
7 - Meteor

Haven't really tried this solution since I never used this tool. Looks like a tempting alternative way. 

Labels