Join based on Dynamic field in Macro Iteration

[cuong_brian]

Hi,

I have a list of fields in this format:  Mgr01Username, Mgr02Username, ..., Mgr10Username. 

I want to build an iterative join that with each iteration, it will take on the next field in the list to join.

Example:

Iteration 1:  Mgr01Username = MgrUsername

Iteration 2:  Mgr02Username = MgrUsername

...

However, I don't know how to substitute the join field in the macro to achieve this.  Any help is appreciated.   Thanks!

Brian

[CharlieS]

I suggest using a Control Parameter to make this a batch macro. The Control Parameter will be a macro input from the data stream outside the macro and it can be used by the Action tool to configure the Join.

[DavidxL · Accepted solution]

Note that you also can't use batch macros with In-DB tools, which means you'd be better off using regular tools for querying the database and joining afterwards.

If the amount of data is an issue, you may have to play around with using macros to modify a SQL query "template".

[cuong_brian]

Thanks, I tried the 2nd option.  I created a batch macro with a Control Parameter and generate the SQL on the fly.  Since the data is large, I send the query to the database for the execution.  

[cuong_brian]

Thanks, I created a macro and use the Control Parameter to iterate through different levels of the organization hierarchy.

[Argjent]

Hi, is it possible for you to share your solution? I am working on a similar task right now and would appreciate any help. 

[Sudeshna]

@Argjent , @cuong_brian - can you please share the solution? i'm looking to exactly similar solution to parse through org hierarchy? any help/leads greatly appreciated.

[Sarita_Singh]

Hi Team, I need to put distinct suffixes in file name based on the iteration number , how can i do that? I have a list of suffixes with serial number also present, 
My idea is to join this list with iteration number and pick the correct suffix, 

Can anyone let me know how I can use the Iteration no. field in join tool ?