Data360 Analyze

Hi all,

Hoping you can help with the following.

Referring to the 3 data tables below - I need a way to do a dynamic join of data so that Input_Data table is left joined with LEI_Status table to return the status field. It should be joined 3 times, once for each field name in the FieldName table with the results CAT together (union). i.e. join Input_Data.Broker_LEI with LEI_Status.LEI then join of Input_Data.AgentLender_LEI with LEI_Status.LEI then join Input_Data.TriPartyAgentLEI with LEI_Status.LEI. And CAT all 3 together. The field names in the FieldName table contains the column names for the join. These are not known until being pulled in from a config file at run time, hence it can't be hard coded and must be done in a dymanic loop. So far, I was trying to use a join node in a do while loop but with no success. 

Input_Data table
TradeId, Broker_LEI, AgentLender_LEI,TriPartyAgentLEI
1, A, B, C
2, D, E, F
3, H, I , J

LEI_Status Table
LEI, Status
A, Active
B, Inactive
C, Active
F, Inactive
J,

FieldName table
FieldName

BrokerLEI
AgentLenderLEI
TriPartyAgentLEI

much appreciated
Scott

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

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

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?

kind regards

Scott

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

Attached files

Dynamic Enrichment.csv

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

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

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

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

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.

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