Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

ColumnMissingWrongRequiresCleaningInvalidatesRowNull
A00001521860
B01453000
C000180
D180000
D103656860089834535
H00000
L05410036245398
N0000117816440
P000018157887
Q021544672001095761
R000579540
S07973939000

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.

ColumnConsequenceFailNull
BWrong14530
BInvalidatesRow00
CWrong00
CInvalidatesRow180
DWrong00
DMissing180
D1Wrong3656860
HWrong00
HMissing00
LWrong5410
NWrong00
PWrong00
QWrong215446720
RWrong00
RInvalidatesRow579540
SWrong79739390
SMissing00
2 Replies
Not applicable
Author

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

Not applicable
Author

Thanks Erica,

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

Sindy