topic Re: Adding virtual value in QlikView

Adding virtual value

bellesol — Sat, 16 Nov 2024 02:03:57 GMT

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

Re: Adding virtual value

JGMDataAnalysis — Thu, 31 Oct 2019 19:47:08 GMT

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;

Re: Adding virtual value

bellesol — Sun, 03 Nov 2019 07:47:31 GMT

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

Re: Adding virtual value

JGMDataAnalysis — Sun, 03 Nov 2019 16:57:18 GMT

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

Re: Adding virtual value

bellesol — Thu, 14 Nov 2019 08:14:43 GMT

Thanks! exactly what I wanted 🙂