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

joining tables dynamically

Mikis
8 - Asteroid

Hello everyone,

 

I'm trying to join two tables dynamically on a variable number of fields, so I built a batch macro that allows me to input 2 tables and a text string.

This text string is used in the macro to update the raw XML.

I'm new to this so I probably screwed it up (well obviously since it's not working), but I can't figure out what.

Could somebody please have a look why the batch macro is not outputting the right result? I attached the workflow that i got so far.

 

Huge thanks in advance,

Mikis

 

issueJoining.PNG

8 REPLIES 8
ddiesel
13 - Pulsar
13 - Pulsar

Hi @Mikis

 

I was going to take a stab at answering your question, but I get an error when trying to open the workflow.

 

There was an error opening "C:\Users\x\Downloads\flexible join question\joining via batch macro.yxmd":

This workflow was created by a more recent version of Alteryx, and may contain tools or functionality not present in this version. Alteryx does not support using an earlier version of Alteryx to open a workflow created with a newer version. For best results, download the latest version of Alteryx.

Do you still want to attempt to open the workflow?

 

I think I'm on the most recent version of Alteryx Designer: 11.7.4.37815. What version are you on?

CharlieS
17 - Castor
17 - Castor

@ddiesel

 

The workflows were built in v2018.1. If you open the module and macro in a text editor like Notepad, you can just change the "2018.1" here to a lower version like "10.5" and it'll work in most cases where newer features are not used.

 

XMLVersionEdit.png

 

 

ddiesel
13 - Pulsar
13 - Pulsar

@CharlieS awesome tip! I'm in.

ddiesel
13 - Pulsar
13 - Pulsar

@Mikis Sorry, I'm not able to solve. Once I got in, the problem was not what I suspected.

 

 

@CharlieS Do you have a solution in mind?

 

CharlieS
17 - Castor
17 - Castor

@Mikis

 

Solution Attached. I crudely reconstructed the Outer XML from the Join tool in the macro with the Text Inputs. Let me know if you have any trouble opening the file.

 

 

EDIT: Another thing that should be added is a Dynamic Select tool after the Join tool inside the macro with the following formula. It will deselect any field that are renamed with the prefix "Right_" due to duplication between inputs.

 

IF left([Name],6)=="Right_" THEN "false"
ELSE "true" ENDIF

Mikis
8 - Asteroid

Brilliant, works like a charm! Thanks CharlieS and thanks ddiesel for trying :)

 

@CharlieS: So if I understand properly, my mistake was that I did not replace the 'full' XML string, but only the part that needed to be replaced. So the batch macro basically didn't know what to change?

There was no mistake in the batch macro? Or did you change it as well?

 

Thanks a lot,

Mikis

CharlieS
17 - Castor
17 - Castor

@CharlieS: So if I understand properly, my mistake was that I did not replace the 'full' XML string, but only the part that needed to be replaced. So the batch macro basically didn't know what to change?

There was no mistake in the batch macro? Or did you change it as well?

Here's how you could have modified your version to get it working:

Macro: The original macro only had an action updating "JoinInfo[2]". Add a second Action tool to update "JoinInfo[1]" with the same question input. In this example, you have the same fields in both inputs so the same XML update can be used for both JoinInfo XML updates. 

Module: Delete and connect around Formula(23) since it adds unnecessary outer XML tags.

 

 

In the Community post below, @jdunkerley79 has a good visual example of the difference between an inner and outer XML update:

https://community.alteryx.com/t5/Data-Preparation-Blending/Dynamic-Transpose-Key-Field-Summary-Field...

Mikis
8 - Asteroid

Ahh that makes total sense. So I need an action tool for every piece that I want to manipulate (assuming I use Inner XML, which sounds more flexible to me).

I was trying to manipulate both JoinInfo[1] and [2] within that one action tool, but couldn't figure it out.

 

I'm pretty sure I'm gonna be using those things frequently, so you saved me hours in figuring it out. Cheers!

Labels