2 Replies Latest reply: Oct 15, 2014 7:50 AM by Sindy Donaldson-Buist

# Sumproduct

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

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

Thanks Erica,

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

Sindy