We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Dynamic join

Comments

7 comments

  • Avatar
    Gerry Mullin

    You can do this with the Do While Conditional and rather than hardcoding a field name in your Join node. In this data flow you'll see fields MatchKey and MatchKeyValue, they will contain Broker_LEI and the value inside that field on iteration 1, AgentLender_LEI and its value on iteration 2 etc. That way the Join (I used a Merge) will always join the Status table based on the MatchKeyValue field and the LEI field from the Status Table.

     

    The Do While approach can be a bit confusing at first glance if you're not used to it. I generally use one with a loop counter and I just plus 1 when I'm ready for the loop to move onto the next iteration. You'll see several BRD nodes in this data flow because I am writing out the result on each iteration and appending to it each time. Rather than use the append option in BRD I am reading the file using Cat to union it to the current result and outputting a new file.

     

    Attached files

    LEI Loop.lna

     

    0
    Comment actions Permalink
  • Avatar
    Scott Perry

    Hi Gerry, that's awesome thanks so much!  

    Whilst investigating this further (since I posted earlier), I realized what I ideally want is the results all on one row for each tradeId. Think this would involve the creation of some new columns on the fly to store the results with one new column for each fieldname so that the results look like the below. Please can you provide an update to the graph to do that?

     

    TradeId Broker_LEI AgentLender_LEI TriPartyAgentLEI Broker_LEI_Status AgentLender_LEI_Status TriPartyAgentLEI_Status
    2 D E F     Active
    3 H I J     Inactive
    1 A B C Active Inactive  

     

    kind regards

    Scott

    0
    Comment actions Permalink
  • Avatar
    Scott Perry

    Attached a file of the desired output as it didnt paste very well above, much appreciated.

     

    Attached files

    Dynamic Enrichment.csv

     

    0
    Comment actions Permalink
  • Avatar
    Gerry Mullin

    I'm not sure if your TriPartyLEI_Status column is pasting right, your values look different to what I was expecting. It looks to me like you want to ignore all the Nulls on the status side. I split out the status into 3 columns and used the Aggregate node to pick the Max value of them (not the Null ones).

     

    Attached files

    LEI Loop.lna

     

    0
    Comment actions Permalink
  • Avatar
    Scott Perry

    Hi Gerry, 

    thanks that gives the right output but the 3 new status columns have been hardcoded in the transform node. Is there a way to create the 3 new status columns dynamically at the start of the graph and then dynamically write to them dependent on the match key? 

    Also now that we don't need to copy the data each loop (as I want to create 3 output rows instead of 9) is it possible to loop the data itself and just update it each loop for each new column? (rather than write it to a BRD file and retrieve each time)

    Kind regards

    Scott

    0
    Comment actions Permalink
  • Avatar
    Gerry Mullin

    If I was doing this, I think I would clean up the field names outside the loop. My approach would be to dynamically create the columns by pivoting your field names table so that they would they would become columns. Cat those with a union to the result so that they would be in the main dataset with Null values. Then using a Transform you would use the value in MatchKey field to populate the newly created columns. If the names are dynamic I believe you would have to do the display within a Transform rather than using the Aggregate node, which could be done with a little bit of python coding.

     

    That's not to say you could not do it within the Loop itself and set the output in the format you want and append that way. My understanding of the problem is that you need to feed in the entire TradeId table on each iteration so I think it might be a little harder to get the display perfect within the loop itself because I'm not sure how you would get the total picture without putting all the data in one place and then cleaning it up for display purposes. Hope that helps.

    0
    Comment actions Permalink
  • Avatar
    Scott Perry

    Hi Gerry, yep thanks agreed. I think I should be able to do this.

    0
    Comment actions Permalink

Please sign in to leave a comment.