Macro Struggle -- Dynamic Join that must meet TWO Criteria Points

[laurennewton]

Hi community! I'm in need of some help strategizing how to accomplish my goal. I am currently taking a very manual workflow and attempting to automate it.

Goal: Match incoming database records with a running file of user inputs that comes from an Analytics App. I need to match multiple groups data (that can be any combination of different at the same time!) 

Background: I am attempting to repurpose a dynamic join macro I already use; however, this macro is designed to match one field at a time, but my current need is to match on the combination of fields one group is using.

Example: I have Group 1 that has a program number of 1122BA and a project number of 1245F I want to associate any document numbers coming in from the database source that match on BOTH that program number and project code. The reason the combination is important is because several groups could have the same Program, Location or Project code. Thus, by matching on the combination I'm assured that the correct document numbers from the database source are associated with the correct group.

I have attached a Dummy Workflow with fake data as well as the Dynamic Filter I am attempting to repurpose. Any thoughts on how I can accomplish my goal?

[ArnaldoSandoval]

Hi @laurennewton 

If I understood your challenge you have an existing macro, "DynamicJoin" which applies two filters in tandem, e.g. If filter1 is true, then if filter2, where filter1 is [Field1]="ABC" and filter2 is [Field2]="XYZ", this pattern is equivalent to CombinedField = [Field1] + "|" + [Field2], then your macro only need a single filter which is: [CombinedField] = [Field1] + "|" + [Field2] which is what I implemented. The reason for this change is to simplify the macro workflow.

Workflows Canvas:

Main Data Workflow

Macro:

Macro Workflow

The DynamicJoin-01 macro configuration:

• The Main Data Workflow define the field: JoinField on the "Dummy Database Source" stream and "Dummy User Data" stream as: [Program] + "|" + [Project]
• In the Macro's Group By tab select [JoinField] for both dropdowns: Control Groupby Field and Input1 GroupBy Field.
• Select the field [JoinField] at the Macro's Questions "Choose Field: Select Join-Field" dropdown.

hth

Arnaldo

Attachments:

• Main_Data_WF-01.yxzp: Including the macro.
• Main_Data_WF-01.yxmd: you need to associate the macro once you download this attachment.
• DynamicJoin-01.yxmc this is the macro by itself.

[laurennewton]

@ArnaldoSandoval thanks so much for replying to my post! there is one additional point that i left out in my original post that is tripping me up currently. the example i used i gave Program and Project as possible fields to match on. However, the incoming data from the database is always consistent and every record will have 7 fields that I will need to possibly match on (so 5 more in addition to the examples I gave) and the data coming from the user could be any combination of those 7 fields. Sometimes is Program and Project other times its Project and Location and so on and so forth. So when I go to create the JoinField it could look something like this "112234||||ZRT5421", all of those ||| indicates filter options not used by a user, they only chose to use Project and Location. When the join attempts to to compare the incoming JoinField to the database data it cannot properly match because the format is not the exact same.

I attempted to edit the macro join to be a Contains filter (I've attached my attempt here) however it throw an error stating "Parse Error at char(0): Unknown variable "112234"...have i incorrectly structured the macro? 

Hoping my description makes sense, if not I can upload another dummy workflow.

[ArnaldoSandoval]

Hi @laurennewton 

Would it be possible for you to share a sample of your data, the header with up to 5 records of sample data?

Cheers,

Arnaldo

[laurennewton]

@ArnaldoSandoval yes! see attached. the macro embedded was the one you sent over.

[ArnaldoSandoval]

Thank @laurennewton 

Now I understand how this analysis evolved, let me think some ideas before I post back a solution, I planning to enhance it as an Analytical Application.

Arnaldo

[ArnaldoSandoval]

@laurennewton 

Enhancing the workflow to handle multi-field selection:

• Currently the macro DynamicJoin-v01 joins two streams on the JoinField column.
• The enhancement allows users to select upto 7 fields:

• The user may select any combination of fields, at least one should be selected.
• If the user select [Org], [Program] and [Location] the interface will produce the JoinField as:

[Org] + '|' + [Program] + '|' + [Location]

• Both Data Stream sources should present the fields (columns) with the same sequence, like this:

The Data Stream 2 ensure the fields sequence via the Select (60)

• The Fields selector is implemented in the workflow with a ListBox selector and an Action Tool, shown

• This Listbox and Action tools directly affect the Select (35) and Select (60) making these Select tools dynamic.
• The Action Tools do the magic of making the Select tools dynamic, their configuration is simple, check it out in the workflow.
• The Data Stream 1 is prepared by this section of the workflow, its Summarize tool produces the JoinField.

• The Data Stream 2 introduces the Dynamic Rename tool, and the Ensure macro by CReW; the Cross Tab tool mischief behaviour replaces spaces with underscores, so the Dynamic Rename reverse them back to spaces.

While the CReW Ensure Fields is used to ensure all the fields are present in the data stream.

• Splitting the source data into rows is done by 7 Text to Column tools, one per field of concern.

• The Select tool is dynamic, linked with the Action tool from the interface controls explained earlier, we tag the data with RecordID, transpose the data, and the Summarize tool actually create this stream JoinField.

• Now you should click on the Wizard button, top-right of the canvas to run this Analytic Application.

Attachments:

The attached yxzp file contains the Analytic workflow, the CReW Ensure Fields and the DynamicJoin-01 (with no changes)

hth

Arnaldo

[laurennewton]

@ArnaldoSandoval this is amazing thank you! one quick note as i'm attempting to test this out, the CReW macro is throwing an error that it cannot find the macro "CReW_Version.yxmc". I think its because my computer is not recognizing it here when I open up the Macro WF...any way you provide me that macro? 

[ArnaldoSandoval]

Hi @laurennewton 

You may download them and install on your workstation, here is the link CReW Macros 

It is good to know about this validation, I used this macro before, I do not know where I found it but certainly something could be done but first test the workflow, then let us know if your organization has issues installing CReW Macros.

Cheers,

Arnaldo

[ArnaldoSandoval]

@laurennewton 

This is the original 2013 source code for this macro: Alteryx: Ensuring fields are in a data stream I will download this version as I did before, I am more confident customizing it.

Arnaldo