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

    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
        • 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