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

Carlithian

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

Action Tool Config.png

General Macro Layout.png