Adding virtual value

[bellesol]

HI,

I have the following two tables that connected with the column "Sub-Department"

Expenses:

Department	Sub Department	Expense Type	Expenses$
Production	Production-A	Salaries	100
Production	Production-A	Salaries	399
Production	Production-B	Energy	488
Production	Production-Management	Maintenance	400
Development	Development-A	Energy	100
Development	Development-A	Energy	200
Development	Development-B	Salaries	450

Output:

Sub Department	Output
Production-A	300
Production-B	400
Production-C	500
Production-D	600
Development-A	700
Development-B	500
Development-C	567

Is it possible to create a "virtual" department that is a calculation of two departments, the name of the "virtual" department will be a concatenation of the real departments =  "Production+Development " and it will be made from the sub-department: "Production-A" and "Development-A"

the table that will be displayed for the user when he will select the department "Production+Development " he will get the following table:

(and when there is no filter the user will see all the departments, including the new one)

Department	Sub Department	Expense Type	Expenses$	Output
Production+Development	Production-A	Salaries	499	300
Production+Development	Development-A	Energy	300	700

[JGMDataAnalysis]

Something like this?

Expenses:
NOCONCATENATE
LOAD *, 
     If(Index("Sub Department", 'Production-A') Or 
     		Index("Sub Department", 'Development-A'), 
     			'Production+Development', Department) AS "Virtual Department"
INLINE [
    Department,	 Sub Department,	    Expense Type, Expenses$
    Production,  Production-A,		    Salaries,	  100
    Production,  Production-A,		    Salaries,	  399
    Production,  Production-B,		    Energy,		  488
    Production,  Production-Management, Maintenance,  400
    Development, Development-A,			Energy,		  100
    Development, Development-A,			Energy,		  200
    Development, Development-B,			Salaries,	  450
];

Output:
NOCONCATENATE
LOAD * INLINE [
    Sub Department,	Output
    Production-A,	300
    Production-B,	400
    Production-C,	500
    Production-D,	600
    Development-A,	700
    Development-B,	500
    Development-C,	567
];

Or this...

Old2NewDep_Map:
MAPPING LOAD 
	Lower("Sub Department"), '<' & "Virtual Department" & '>'
INLINE [
    Sub Department, Virtual Department
    Production-A, 	Production+Development
    Development-A,	Production+Development
];

Expenses:
NOCONCATENATE 
LOAD *, 
	 If(IsNull(tmpVirtualDep), 
        Department, tmpVirtualDep) AS "Virtual Department"
;
LOAD *, 
     TextBetween(
     	MapSubString('Old2NewDep_Map', Lower("Sub Department")), '<', '>'
	 ) AS tmpVirtualDep
INLINE [
    Department,	 Sub Department,	    Expense Type, Expenses$
    Production,  Production-A,		    Salaries,	  100
    Production,  Production-A,		    Salaries,	  399
    Production,  Production-B,		    Energy,		  488
    Production,  Production-Management, Maintenance,  400
    Development, Development-A,			Energy,		  100
    Development, Development-A,			Energy,		  200
    Development, Development-B,			Salaries,	  450
];

Output:
NOCONCATENATE
LOAD * INLINE [
    Sub Department,	Output
    Production-A,	300
    Production-B,	400
    Production-C,	500
    Production-D,	600
    Development-A,	700
    Development-B,	500
    Development-C,	567
];

DROP FIELD tmpVirtualDep;

[bellesol]

Thank's for your replay!

Maybe I didn't explain myself correctly, my goal is when a user doesn't select a  department, he will see all of the departments including the new one.

The table shown will be:

Department	Sub Department	Expense Type	Expenses$	Output
Production+Development	Production-A	Salaries	499	300
Production+Development	Development-A	Energy	300	700
Production	Production-A	Salaries	499	300
Production	Production-B	Energy	488	400
Production	Production-Management	Maintenance	400	0
Development	Development-A	Energy	300	700
Development	Development-B	Salaries	450	500

[JGMDataAnalysis · Accepted solution]

Try whit this...

Expenses:
NOCONCATENATE
LOAD * INLINE [
    Department,	 Sub Department,	    Expense Type, Expenses$
    Production,  Production-A,		    Salaries,	  100
    Production,  Production-A,		    Salaries,	  399
    Production,  Production-B,		    Energy,		  488
    Production,  Production-Management, Maintenance,  400
    Development, Development-A,			Energy,		  100
    Development, Development-A,			Energy,		  200
    Development, Development-B,			Salaries,	  450
]
;
CONCATENATE(Expenses)
LOAD
	'Production+Development' AS Department,
    "Sub Department",
    "Expense Type",
    Sum(Expenses$) AS Expenses$
RESIDENT Expenses
WHERE
	Index("Sub Department", 'Production-A') 
    	Or Index("Sub Department", 'Development-A')
GROUP BY "Sub Department", "Expense Type";

Output:
NOCONCATENATE
LOAD * INLINE [
    Sub Department,	Output
    Production-A,	300
    Production-B,	400
    Production-C,	500
    Production-D,	600
    Development-A,	700
    Development-B,	500
    Development-C,	567
];

[bellesol]

Thanks! exactly what I wanted 🙂