Dynamic Configuration of a Join Tool within a Macro

[AM11]

Does anyone have any experience with utilizing a join tool within a macro and configuring it to dynamically update the joined fields each time the macro is run? I tried to configure left and right join inputs separately but I cannot make it work (I used the Action tool with the List Box or the Drop Down tools to configure the joined by criteria).

Here is an example of what I am trying to accomplish. I want to configure the join tool within a macro to dynamically update the joined fields by utilizing two different join fields:

 - Field/Criteria 1: match "ID Level 1" or "ID Level 2" from Dataset #1 to "ID" from Dataset #2, and;

 - Field/Criteria 2: match "Qty" or "Amount" from Dataset #1 to "Qty" or "Amount" from Dataset #2.

I want these fields to be dynamic to allow me to switch the join fields (from ID Level 1 to ID Level 2, and Qty to Amount) outside the macro. Is this possible at all? Thank you

[DawnDuong]

hi @AM11 

I have a "simplistic" short-cut.

In each of the 2 input create a new column called "Field to Match" which is basically set to be the default selected column. then the Interface tool is used to update the "Field to Match" with the the user-input fields.

This case, your join is static: i.e. it is always set to have "Field to Match" on both inputs as the joining condition.

however the "Field to Match" itself is being set based on user inputs. This workaround is just to make it easier if you want to use the "matched column" downstream. Alternatively, you can use the standard Interface Tool to update the Join 1 and Join 2 inputs, too.

Apologies for not including a sample workflow as I am occupied with something else at the moment. Hope the above schematics makes sense.
Cheers,
Dawn.

[AM11]

@DawnDuong thank you for the shortcut idea, it is a genius idea and works perfectly! I just have to update the "Field to Match" column every time before I use the macro within the workflow.

Assuming you have the time, I would be interested to hear about using the standard Interface Tools to update the Join 1 and Join 2 inputs. I was wondering if this option would optimize the workflow better.

Thank you

[DawnDuong]

Hi @AM11 

glad it helps! It just happens that i will be occupied with work and travel till mod next week.

i actually learnt to use the interface tools via the interactive lessons on Analytic Apps and Macro as well as the recorded videos on Interface tools.

if time permits for you, i’d highly recommend those resources as they do not only teach dry basics but also share use cases and configuration tips.

cheers

dawn

[AM11]

Thank you @DawnDuong, I will try to find those trainings 🙂 Thank you again for all your help and showing me how to think outside the box