topic Mapping in Expression Disables Subtotal in QlikView

Mapping in Expression Disables Subtotal

Tue, 30 Nov 2010 17:38:46 GMT

Hi guys,

I have created a pivot table which uses the basic sum function. However, my expression also integrates a sign mapping field (created in the script from an excel mapping file), so the expression is: sum(Exp) * ExpSignType. (results in red are negative/black in positive) This results in the table below:

ExpType	Team1	Team2	Team3	Team4	Team5	Team6	Total
A	7,312,710	3,401,868	15,515,633	27,910,919	2,196,218	10,369,605	66,706,953
B	6,380	17,917	1,092,844	1,350,199	91,243	90,788	2,649,371
C	0	0	0	1,383	0	0	1,383
D	6,839	0	15,997	101,572	0	0	124,408
E	0	0	(15,997)	(108,411)	0	0	(124,408)
F	0	0	0	(3,679)	0	0	(3,679)
G	0	(38,587)	0	(120,638)	(31,165)	0	(190,391)
H	(117,220)	(36,111)	(276,027)	(649,683)	(55,559)	(3,936)	(1,138,536)
I	(195,806)	0	(125,411)	(130,548)	(4,272)	0	(456,037)

This gives the correct values but one problem i have is that when I do this i am no longer able to tick the 'partial sums' button and therefore cannot give the subtotal for each team. Does anyone know a way around this? It would be pretty difficult to create the expression in the script so any other ideas ould be appreciated.

Thanks,

Mapping in Expression Disables Subtotal

Miguel_Angel_Baeyens — Tue, 30 Nov 2010 18:10:17 GMT

Hello,

You shouldn't see any difference with

sum(Exp * ExpSignType)

(haven't tested though). Even better, you can create a new field in your load script

Exp * ExpSignType AS DisplayExp,

and use it in the expressions instead of multiplying.

Hope this helps

Mapping in Expression Disables Subtotal

Tue, 30 Nov 2010 18:38:33 GMT

Thanks MIguel,

But changing the parentheses changes the figures quite drastically unfortunately. The expsigntype resides on a separate table to exp which I could join to create the expression but i was hoping there would be another way around it.

Mapping in Expression Disables Subtotal

Miguel_Angel_Baeyens — Tue, 30 Nov 2010 20:47:20 GMT

Hello,

You don't need to join (well, it will depend on the load script performance, but I don't think you need) but I'd use a mapping table to get it on the same table that exp, kind of

ExpSigntypeTableMap:MAPPING LOAD ID, // This is the key field between both tables expsigntypeRESIDENT Table1; Data:LOAD *, ApplyMap('ExpSigntypeTableMap', ID, 1) AS ExpWithSign // This will return the corresponding expsigntype for the ID // "1" would be the default value, is optionalFROM Source;

Hope this helps

Mapping in Expression Disables Subtotal

johnw — Tue, 30 Nov 2010 23:38:08 GMT

Perhaps this?

sum(aggr(sum(Exp)*ExpSignType,ExpType,Team))

Mapping in Expression Disables Subtotal

Wed, 01 Dec 2010 10:11:37 GMT

I think both answers look as though they would work but as I have a number of tables concatenating onto one another I'm going to use the expression approach which works for me.

Thanks both of you for your input, much appreciated!

Mapping in Expression Disables Subtotal

Thu, 02 Dec 2010 10:00:45 GMT

Looks like I'm actually going to have to try Miguel's approach. the performance of the expression seems to make the chart extremely slow unfortunately.