How to Connect Data Modules that have different Dimensions

neg177
neg177
in Modeling

Hi, 

 

I need advice on how to connect two separate Data Modules with different dimensions. I was thinking if there is a way to create a mapping module to connect them. They both have a similar LI's called Merchant ID. A screen snip is below of the two modules. 

 

neg177_0-1646248667128.png

Blue Print 

neg177_1-1646248716488.png

Common LI in the Modules 

neg177_2-1646248776176.png

 

 

Thank you 

Tagged:

Best Answer

Answers

  • neg177
    neg177

    Hi, 

    Thank you Timmy. I'm going to give it a shot. 

  • neg177
    neg177

    I did have a quick question. What would I load into the Mapping Module to get the Date Applied populated? 

     

    neg177_0-1646676897313.png

     

  • TimothyThomas
    TimothyThomas

    Hi @neg177 ,

     

    In my example I manually chose the dates. If there's not a huge amount of data then this is probably a reasonable way to go about it, but if your data modules are rather big then it wouldn't be. Is there any line that contains the same information between the two modules that could be used to link up? For example, is the merchant name within the "Name" line item in your D02 Legal Expense Data module?

     

    If so, you could use a FIND formula to find the name within that text line item and create a flat list from those names, then use that list to perform a lookup for your formula. That's just an example... but to create the formula there would have to be some sort of link between the modules to use as a lookup.

     

    Thanks,

    Timmy

  • neg177
    neg177

    Hi Timmy, 

     

    My apologies, but the MID LI in our D02 actually is populated with data, and we are trying to make the connection to see the two data sets together. So we want to be able to link the data sets that have two different mapping criteria's. 

     

    neg177_0-1646679960190.png

     Would you still use the same approach that you first showed, or since that MID LI is populated with data would you recommend a different approach? 

     

    Thank you for your help

  • TimothyThomas
    TimothyThomas

    Hi @neg177 ,

     

    Ah, I see - no worries, it can be tough to get the right message across in the forums! Hoping I understand the issue properly now... so basically, you would like to bring lines from one of the modules over to the other to consolidate them in one module?

     

    If I'm understanding that correctly, you can still use a simple lookup, though you don't even really need the intermediate mapping module. From what I can tell from the small subset of data, it appears that there is a many-to-one relationship between Contract IDs and the Date Applied list, but each Date Applied has a one-to-one relationship with Contract IDs. So you can bring in line items from the D02 module into the D01a module using a lookup.

     

    You'll need a line item identifying the Contract ID list in D01a, which will be used for your mapping. It is a simple FINDITEM formula, I'll attach a screenshot. From there, you can choose a line item from D02 and use this new mapping line item to bring any of the LIs in D02 over to D01a - see attached for an example for the "Financial Row" line item.

     

    Do let me know if I'm still not fully understanding the issue... otherwise hope this helps!

    Thanks,

    Timmy

Categories