Link 2 data sets

yatin.ajbani

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

TristanS

@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