Join Variable Fields and Variable Number of Fields

[riverotoledo_21]

Hello!  

I'm facing a particular challenge, and i need some help or additional development.  

I would like to Join data based on multiple structures used in our company.

(e.g.: Store Structure, Financial Accounts Structure, and loads of additional structures need to deal with all the particularities of our business.)  

The Store Structure has 8 Levels (columns) (S1, S2, S3, S4, S5, S6, S7, STORES)

The Financial Accounts Structure has 6 Levels (columns) (F1, F2, F3, F4, F5, ACCOUNT)  

I need to Join multiple and variable fields and Variable numbers of fields.  

As metrics can use any particular combination with the structures above, that would make 16,383 possible configurations of the Join Tool.

As its seems mad to build all that Joins, i would need a solution to dynamically change the Join tool configuration.  

I thought something like building strings that could be imputed (with an Action) as configuration in the Join Tool:  

String 1 - Fields Left                      String 2 - Fields Right              String 3 - Number of Fields

S1, F4, F5                                      S1, F4, F5                                3                                                    

S1, S2, F2, ACCOUNT                  S1, S2, F2, ACCOUNT            4  

Today, we cannot input in the Join tool configuration, with an action:

- choose the numbers of Fields
- Input More Than One Field per Action Tool

If i had the possibilities above i could make a macro that changes dynamically and handles all possible variations.

Any thoughts?

Thanks in advance!!

[fmvizcaino]

Hi @riverotoledo_21 ,

Instead of using your macro to update the join tool, I would suggest you to use a batch macro to update a formula tool expression where you create a combined  key concatenating all the columns into one. Does it work for you?

Best,

Fernando Vizcaino

[jacob_kahn]

@riverotoledo_21 

I would love to be of assistance, but I do not understand the ultimate goal from your description. Perhaps you can try opening up exactly what you are looking to accomplish in another way - regardless, good luck.

[carlosteixeira]

Hi @riverotoledo_21 

All right?

An idea is attached.
If it doesn't work for manos I think it's a starting line for a different rationale.

Any doubts I am available.

Hugs

[riverotoledo_21]

Thank you both @fmvizcaino and @carlosteixeira  for the help!

The solution used a mix of both ideias.

What i've done:

I created a string that contains the syntax used by afilter tool that is capable of filtering multi/variable levels.

I also added a formula to write a key that will be used to add additional info.

That does the trick of what looks like a "multi and variable field dynamic Join Tool".

Cheers!

[Skytop]

Hello,

I have a similar use case to solve.

Can you please share how did you build the Custom formula?

thanks.

[riverotoledo_21]

Hello Skytop!

For example for one particular execution of the filter macro, I have 2 conditions:

•  Column A = "1"
•  Column B = "2"

My solution:

1. Lots of data manipulation in order to transform the conditions you have into something like this:

Filter Execution	Filter Condition	Condition String
1	1	Column A = "1"
1	2	Column B = "2"

2. Then concatenate using the Summarize Tool:

Config:

• Group by - Filter Execution
• Concatenate - Condition String - Separator set to " AND "

That will provide you with the custom string:

Column A = "1" AND Column B = "2"

This String is exactly what you will use to update the Expression in the Filter, inside the Batch Macro.

Hope it helps!

Pedro

[Raquelmartingu]

could you upload the macro please?