topic Re: Sumproduct in QlikView

Sumproduct

Wed, 15 Oct 2014 09:41:12 GMT

Hi,

I have searched through various similar questions but none seem to resolve my problem. I have two separate excel sheets one in which I do some if statements for a calculation with a one on one relationship.

Column	Missing	Wrong	RequiresCleaning	InvalidatesRow	Null
A	0	0	0	0	1521860
B	0	1453	0	0	0
C	0	0	0	18	0
D	18	0	0	0	0
D1	0	365686	0	0	89834535
H	0	0	0	0	0
L	0	541	0	0	36245398
N	0	0	0	0	117816440
P	0	0	0	0	18157887
Q	0	21544672	0	0	1095761
R	0	0	0	57954	0
S	0	7973939	0	0	0

I am trying to get the results of this to show in a one to many format with the second spread sheet (Fail & Null) I can do this using the sumproduct in excel =SUMPRODUCT(($A$2:$A$13=A18)*($B$1:$F$1=B18)*($B$2:$F$13)) but cant seem to get a similar calculation in qlik.

Column	Consequence	Fail	Null
B	Wrong	1453	0
B	InvalidatesRow	0	0
C	Wrong	0	0
C	InvalidatesRow	18	0
D	Wrong	0	0
D	Missing	18	0
D1	Wrong	365686	0
H	Wrong	0	0
H	Missing	0	0
L	Wrong	541	0
N	Wrong	0	0
P	Wrong	0	0
Q	Wrong	21544672	0
R	Wrong	0	0
R	InvalidatesRow	57954	0
S	Wrong	7973939	0
S	Missing	0	0

Re: Sumproduct

Wed, 15 Oct 2014 10:40:52 GMT

Hi SCD Buist

You can't do such a function in QlikView - it's all table based.

I suggest that you load the first table in "unpivoted" from fields Column - > to Invalidates row. You can do this using the cross table function in the script:

DATA:

CrossTable(Column, Consequence)

LOAD Column, Missing, Wrong, RequiresCleaning, InvalidatesRow

FROM .....

You can then join the null column on separately.

Erica

Re: Sumproduct

Wed, 15 Oct 2014 11:50:19 GMT

Thanks Erica,

Unfortunately I cannot seem to get the cross table to work. I get syn keys and missing data

Sindy