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 |
Solved! Go to Solution.
Hi @BRRLL99
It's not so much dynamic, as just being three separate joins. You could put this in a macro, but not necessary.
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>
can you please share the file or
step by step screen shot
for Action tool which formula needs to be used
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
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/