Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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 🙂