QlikView replacing nulls with zeros in a table

[#1]

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

[#2]

Edit:

Sorry the first output when only B1, C1 and D1 are selected is this: (I had Name_A in by mistake)

ID           Name_B1          Name_C1          Name_D1
1                  6                         3                         4

2                  8                         0                         3

[#3]

Hi

you can cheat a bit with

if (sum(fieldname)=0, '-', sum(fiueldname))

regards

chris

[salto]

Hello,

in this case my chart shows it as '-'. Please check and let me know.

Hope this helps.-

[Jason_Michaelides]

Are you saying that you should have a 3rd row when just B1, C1 and D1 are selected, and the value for each expression should be Null, not 0?  Or should you omit the 3rd row as the sum of all values are zero?

[#6]

Hi Chris, the issue with that in some cases A could have a value and the rest of the fields could be zero, in which case I wouldn't want to exclude the entry.

SC

[#7]

@SALTO You mean you have the same situation and  your chart shows - instead of zeros in that instance?

[salto]

That's right, .. have you checked the file I attached? It shows '-' instead of '0'.

[#9]

@SALTO, thank you, yes I've opened it and I think there might be an issue with the underlying data model at my end- we have the exact same properties for the table, but mine shows a different view. I will investigate further and post my findings.

@Jason, I shoud omit entries when an entry ID for A doesn't have any results for B1, C1, D1.

[swuehl · Accepted solution]

Maybe like attached, using disabled suppress zero values on presentation tab and a condition in your expression:

=if(count(Number),Sum(Number),'-')