Dynamic Joint using Batch Macro ???

[BRRLL99]

I'm looking to join two different tables based on my base_product requirement

If base product column = other than trade date and trade party should be joined

product column = Option than trade date, trade party, put columns, call amount should be joined.

product column = Swap then Strike price and deal notional amount should be joined

basically i should have 3 outputs with different joints in Macro

Please let me know this is possible using Batch macro or any other macro

Table - 1

ID	BASE PRODUCT	TRADE DATE	TRADE PARTY	PUT AMOUNT	CALL AMOUNT	STRIKE PRICE	DEAL NOTIONAL AMOUNT
T001	OTHER	23-01-2023	84687	0.88	22	100	50
T002	OTHER	25-01-2023	ABC-991	0.1	3	100	50
T003	OPTION	22-01-2023	33219	1	44	100	50
T004	OPTION	23-01-2023	ERD098	2	55	100	50
T005	SWAP	24-01-2023	HASD09	3	77	100	50
T006	SWAP	26-01-2023	HASD08	4	88	100	50

Table - 2

ID	TRADE DATE	TRADE PARTY	PUT AMOUNT	CALL AMOUNT	STRIKE PRICE	DEAL NOTIONAL AMOUNT
T001	23-01-2023	84687	0.88	22	100	50
T002	25-01-2023	ABC-991	0.1	3	100	50
T003	22-01-2023	33219	1	44	100	50
T004	23-01-2023	ERD098	2	55	100	50
T005	24-01-2023	HASD09	3	77	100	50
T006	26-01-2023	HASD08	4	88	100	50

[PhilipMannering]

Hi @BRRLL99 

It's not so much dynamic, as just being three separate joins. You could put this in a macro, but not necessary.

[Carlithian]

So you can do this using the  the control parameter to interact with the join tool you update the XML. 

Make sure the input to the control parameter is the  Base Product, and you can build and if statement for each of those options to have a different xml setting for the Join

Depending on how much control you want of the join at run time, or how reuseable you would like the tool to be is will change how complicated it is to build.  But hopefully this will put you on the right track

So for example 
If Base Product = Option 
then the xml that you want the Join to have is ; 

<Configuration joinByRecordPos="False">
<JoinInfo connection="Left">
<Field field="TRADE DATE" />
<Field field="TRADE PARTY" />
</JoinInfo>
<JoinInfo connection="Right">
<Field field="TRADE DATE" />
<Field field="TRADE PARTY" />
</JoinInfo>
<SelectConfiguration>
<Configuration outputConnection="Join">
<OrderChanged value="False" />
<CommaDecimal value="False" />
<SelectFields>
<SelectField field="Right_ID" selected="True" rename="Right_ID" input="Right_" />
<SelectField field="Right_TRADE DATE" selected="True" rename="Right_TRADE DATE" input="Right_" />
<SelectField field="Right_TRADE PARTY" selected="True" rename="Right_TRADE PARTY" input="Right_" />
<SelectField field="Right_PUT AMOUNT" selected="True" rename="Right_PUT AMOUNT" input="Right_" />
<SelectField field="Right_CALL AMOUNT" selected="True" rename="Right_CALL AMOUNT" input="Right_" />
<SelectField field="Right_STRIKE PRICE" selected="True" rename="Right_STRIKE PRICE" input="Right_" />
<SelectField field="Right_DEAL NOTIONAL AMOUNT" selected="True" rename="Right_DEAL NOTIONAL AMOUNT" input="Right_" />
<SelectField field="*Unknown" selected="True" />
</SelectFields>
</Configuration>
</SelectConfiguration>
</Configuration>

[BRRLL99]

can you please share the file or

step by step screen shot

for Action tool which formula needs to be used

[Carlithian · Accepted solution]

So what you need to do is

1. Get the XML config of the join tool for each of you scenarios. I'd make sure the fields you want checked are checked, any order chagnes are done etc. Then go into the XML view of you Join tool configuration window, the icon looks like <> if you do not see this option you need to add it in your user settings.
2.  Set your Action tool to 'Update Raw XML with Formula, and it's the whole join you are affecting, with the 'Update Outer XML' button selected. (Screenshot atached)
3. Create your formula, this snippet here is just an if Base Product = Other do x Else do y so you will need to change it. But this is the starting point, use the XML snippets you got in step one to the appropriate THEN's

IF 
	[#1] = 'OTHER' 
THEN
'<Configuration joinByRecordPos="False">
  <JoinInfo connection="Left">
    <Field field="TRADE DATE" />
    <Field field="TRADE PARTY" />
  </JoinInfo>
  <JoinInfo connection="Right">
    <Field field="TRADE DATE" />
    <Field field="TRADE PARTY" />
  </JoinInfo>
  <SelectConfiguration>
    <Configuration outputConnection="Join">
      <OrderChanged value="False" />
      <CommaDecimal value="False" />
      <SelectFields>
        <SelectField field="Right_ID" selected="True" rename="Right_ID" input="Right_" />
        <SelectField field="Right_TRADE DATE" selected="True" rename="Right_TRADE DATE" input="Right_" />
        <SelectField field="Right_TRADE PARTY" selected="True" rename="Right_TRADE PARTY" input="Right_" />
        <SelectField field="Right_PUT AMOUNT" selected="True" rename="Right_PUT AMOUNT" input="Right_" />
        <SelectField field="Right_CALL AMOUNT" selected="True" rename="Right_CALL AMOUNT" input="Right_" />
        <SelectField field="Right_STRIKE PRICE" selected="True" rename="Right_STRIKE PRICE" input="Right_" />
        <SelectField field="Right_DEAL NOTIONAL AMOUNT" selected="True" rename="Right_DEAL NOTIONAL AMOUNT" input="Right_" />
        <SelectField field="*Unknown" selected="True" />
      </SelectFields>
    </Configuration>
  </SelectConfiguration>
</Configuration>'
ELSE
'<Configuration joinByRecordPos="False">
  <JoinInfo connection="Left">
    <Field field="PUT AMOUNT" />
  </JoinInfo>
  <JoinInfo connection="Right">
    <Field field="PUT AMOUNT" />
  </JoinInfo>
  <SelectConfiguration>
    <Configuration outputConnection="Join">
      <OrderChanged value="False" />
      <CommaDecimal value="False" />
      <SelectFields>
        <SelectField field="Right_ID" selected="True" rename="Right_ID" input="Right_" />
        <SelectField field="Right_TRADE DATE" selected="True" rename="Right_TRADE DATE" input="Right_" />
        <SelectField field="Right_TRADE PARTY" selected="True" rename="Right_TRADE PARTY" input="Right_" />
        <SelectField field="Right_PUT AMOUNT" selected="True" rename="Right_PUT AMOUNT" input="Right_" />
        <SelectField field="Right_CALL AMOUNT" selected="True" rename="Right_CALL AMOUNT" input="Right_" />
        <SelectField field="Right_STRIKE PRICE" selected="True" rename="Right_STRIKE PRICE" input="Right_" />
        <SelectField field="Right_DEAL NOTIONAL AMOUNT" selected="True" rename="Right_DEAL NOTIONAL AMOUNT" input="Right_" />
        <SelectField field="*Unknown" selected="True" />
      </SelectFields>
    </Configuration>
  </SelectConfiguration>
</Configuration>'
ENDIF

As the XML contains " and needs to be treated as a string, ensure you use ' ' around it

Also after a quick test I realised you will need a Filter after the input that table 1 is going into, this can be updated with the same Control Parameter like a normal Batch Macro

[Felipe_Ribeir0 · Accepted solution]

Hi @BRRLL99 

See if this is doing what it is supposed to do:

1-If you want to know more about what is happening with this XML/Macro, take a look:

https://www.thedataschool.com.au/ryan-edwards/update-raw-xml-in-alteryx-macros/