Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bellesol
Creator
Creator

Adding virtual value

HI,

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

Expenses:

DepartmentSub DepartmentExpense TypeExpenses$
Production Production-ASalaries100
Production Production-ASalaries399
Production Production-BEnergy488
Production Production-Management Maintenance400
DevelopmentDevelopment-AEnergy100
DevelopmentDevelopment-AEnergy200
DevelopmentDevelopment-BSalaries450

 

Output:

Sub DepartmentOutput
Production-A300
Production-B400
Production-C500
Production-D600
Development-A700
Development-B500
Development-C567

 

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)

DepartmentSub DepartmentExpense TypeExpenses$Output
Production+DevelopmentProduction-ASalaries499300
Production+DevelopmentDevelopment-AEnergy300700
Labels (3)
1 Solution

Accepted Solutions
JGMDataAnalysis
Creator III
Creator III

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
];

QlikCommunityChallenge_Concatenate.PNG

View solution in original post

4 Replies
JGMDataAnalysis
Creator III
Creator III

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;

 

 

QlikCommunityChallenge_VirtualDep.PNG

 

bellesol
Creator
Creator
Author

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:

 

DepartmentSub DepartmentExpense TypeExpenses$Output
Production+DevelopmentProduction-ASalaries499300
Production+DevelopmentDevelopment-AEnergy300700
Production Production-ASalaries499300
Production Production-BEnergy488400
Production Production-Management Maintenance4000
DevelopmentDevelopment-AEnergy300700
DevelopmentDevelopment-BSalaries450500
JGMDataAnalysis
Creator III
Creator III

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
];

QlikCommunityChallenge_Concatenate.PNG

bellesol
Creator
Creator
Author

Thanks! exactly what I wanted 🙂