Dynamic join multiple keys in JOIN tool

Question

Hi,

My workflow needs to develop a dynamic solution to process data like sql .

I have built a simple batch macro, which allows users to define data table and joining key. However, in the current design, the joining key can only joined once. I need some guidance on how to modify the macro so that users can select multiple keys to join in the workflow.

Current approach could only handle one joining key

MACRO:

workflow:

As above shown, there is limitation for the joining key and action tool in macro could only replace joining key once a time.

I have attched the macro below, pls help to let me know any way to allow Mutiple joining key.  so that able to process user defined joining requirement.               APPRECIATE FOR ANY HELP!!!

table1table2table1 keytable 2 key car

carpriceserialNOSerialnumber carcarpricedateDate

sql joining macro.yxmc

apathetichell · Answer

so there are two ways:

1) use edit raw xml mode on the action tool connecting to the join tool. learn how to edit/control generating the xml. - this is difficult.

2) left stream -> add a recordid-call the field leftid. add a dynamic select -> check for [Name] in ("leftid","test") -> have your action tool create a "," split list. -> have it replace test in the dynamic select. add a transpose. - recordid is your key field, any other field (dynamic) is being transposed

right side -> follow those steps but call it rightid. modify your dynamic select to account for that name.

join -> join on name/value. this is now dynamic and can be one value or many.

post join-> group by rightid/leftid/count (these are the only fields you want) -> filter. count has to equal the number of terms you submitted ( you can get this term via an outer app/batch macro logic which controls what you are feeding in -> or try some string terms to split up your list that you send in to the other two action tools).

join leftid-> datastream before dynamic select after creating leftid(returns all original leftid. do the same with the rightid/right datastream. you have now enriched your data.

JASON_OJS · Answer

If user input two different joining key, I want my macro able to join 2 keys together. The batch macro action method seem could only change 1key.

apathetichell · Answer

Do not do this. Use  a dynamic rename instead. Have your join based upon fixed field names. Your action tool modifies the formula in your dynamic rename to change the field that you are renaming to the static fieldname.

Can you use an action tool to change the fieldname in a join - yes. But when it goes wrong it goes really wrong  and troubleshooting it/getting it to work is much harder than the dynamic rename/static name way. note -> add a second dynamic rename after to rename your field back to the original name. (two action tools/one control parameter).