Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Re: QlikView replacing nulls with zeros in a table

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

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

11 Replies
Not applicable

Re: QlikView replacing nulls with zeros in a table


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

Not applicable

Re: QlikView replacing nulls with zeros in a table

Hi

you can cheat a bit with

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

regards

chris

salto
Valued Contributor II

Re: QlikView replacing nulls with zeros in a table

Hello,

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

Hope this helps.-

jason_michaelid
Honored Contributor II

Re: QlikView replacing nulls with zeros in a table

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?

Not applicable

Re: QlikView replacing nulls with zeros in a table

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

Not applicable

Re: QlikView replacing nulls with zeros in a table

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

salto
Valued Contributor II

Re: QlikView replacing nulls with zeros in a table

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

Not applicable

Re: QlikView replacing nulls with zeros in a table

@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.


MVP
MVP

Re: Re: QlikView replacing nulls with zeros in a table

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

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

Community Browser