Join and Join Multiple tool rename problem

[Williamcai]

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

Date	spec	value
1-Jul	a	10
1-Jul	b	20
1-Jul	c	30
2-Jul	b	40
2-Jul	c	50
2-Jul	d	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:

Date1	spec	value1	date2	value2
1-Jul	a	10	null	null
1-Jul	b	20	2-Jul	40
1-Jul	c	30	2-Jul	50
null	d	null	2-Jul	60

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.

[JulioMO]

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: 

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:

Attach you will find the workflow. 

Regards, 

[Thableaus]

Hi @Williamcai 

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

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]

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]

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 · Accepted solution]

@Williamcai 

Not the best scenario here but it could work:

- 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]

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 · Accepted solution]

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

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:

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

[Williamcai]

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