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
|
Thanks in advance
Best 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:
- Add boolean line item "Flag" to both Rel modules and default value to TRUE as per below
- 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
0
Answers
-
Thank you very much @TristanS . this helps
0