11 Replies Latest reply: Dec 13, 2013 5:14 AM by Sarah Cherhabil RSS

    QlikView replacing nulls with zeros in a table

      Hello,

       

      I have a problem which I have been trying to resolve, and my searches for similar issues have failed. I'm very new to QlikView.

       

      I load a table which looks like this:

       

      ID    Name    Number

      1        A            1

      2        A            2

      3        A            3

      1        B1          6

      2        B1          8

      1        C1          2

      2        C1          0

      1        C1          1

      2        D1          0

      2        D1          3

      1        D1          4

      3        D2          8

       

      I create a table at the front end which must look like this, which includes the sums per ID (let's say I select the names B1, C1, D1, note that A has entries which can be relevant to X1 or X2 names) the outcome is this, which is what I want.

       

      ID          Name_A          Name_B1          Name_C1          Name_D1
      1               1                    6                         3                         4

      2               2                    8                         0                         3

       

      Now if I select B1, C1, D1 and A I get this:

       

      ID          Name_A          Name_B1          Name_C1          Name_D1
      1               1                    6                         3                         4

      2               2                    8                         0                         3

      3               3                    0                         0                         0

       

      The zeros in bold seem to be inserted by default even though those names don't have results associated with the same ID. I would've expected those entries to be - (null) rather than replaced with a zero. The goal is to exclude entries where there are null entries for Name_X columns.

       

      I hope this makes sense, any advice or help is much appreciated.

       

      Kind Regards,

      SC