Link 2 data sets

Hi

We have a module with multiple relationships, like manager to employee relationship and employee to office relationship. The end goal is to have 1 line item with all 3 (manager, employee and office) in 1 line item. We created 2 modules from the parent module as below

Module Mgr-Emp Rel

List "Rel"

Rel 1

Mgr1

Emp1

Rel 2

Mgr2

Emp2

Module Emp-Ofc Rel

Rel 3

Emp1

Ofc1

Rel 4

Emp2

Ofc2

how should I display the data as below

Mgr-Ofc rel

List?

Mgr1

Emp1

Ofc1

Mgr2

Emp2

Ofc2

Thanks in advance

Best Answer

  • TristanS
    Answer ✓

    @yatin.ajbani Your modules are structured as a relational model rather than a dimensional model. I get the impression it is replicating the structure of the database table from which the data is sourced from. Ideally, additional transformation should be been performed to restructure the relationships as dimensional rather than relational. But given what you have, there's several ways to get this to work. Quick and nasty way would be:

    1. Add boolean line item "Flag" to both Rel modules and default value to TRUE as per below
    2. Mgr-Ofc rel to be defined as per below

    Then apply a filter to the view such that only combinations where "Is Related?" = TRUE is displayed. This will create a lot of sparsity though. I'd highly recommend you transform the data in to a proper list hierarchy

    L1 Mgr

    L2 Emp

    Module Mgr-Ofc rel

    List: Mgr, Emp

    Line Items

    Formula

    Formula

    Mgr Item

    ITEM(Mgr)

    ITEM(Mgr)

    Emp Item

    ITEM(Emp)

    ITEM(Emp)

    Is Related?

    'Mgr-Emp Rel'.Flag[ANY: 'Mgr-Emp Rel'.Mgr, ANY: 'Mgr-Emp Rel'.Emp]

    'Mgr-Emp Rel'.Flag[ANY: 'Mgr-Emp Rel'.Mgr, ANY: 'Mgr-Emp Rel'.Emp]

    Ofc Item

    IF Is Related? THEN 'Emp-Ofc Rel'.Ofc[FIRSTNONBLANK: 'Emp-Ofc Rel'.Emp] ELSE BLANK

    IF Is Related? THEN 'Emp-Ofc Rel'.Ofc[FIRSTNONBLANK: 'Emp-Ofc Rel'.Emp] ELSE BLANK

    Module Mgr-Emp Rel

    List "Rel"

    Mgr

    Emp

    Flag

    Rel 1

    Mgr1

    Emp1

    TRUE

    Rel 2

    Mgr2

    Emp2

    TRUE

    Module Emp-Ofc Rel

    Flag

    Rel 3

    Emp1

    Ofc1

    TRUE

    Rel 4

    Emp2

    Ofc2

    TRUE

Answers