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

QlikView replacing nulls with zeros in a table

Thu, 12 Dec 2013 12:51:59 GMT

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,

Re: QlikView replacing nulls with zeros in a table

Thu, 12 Dec 2013 12:58:43 GMT

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

Re: QlikView replacing nulls with zeros in a table

Thu, 12 Dec 2013 13:03:56 GMT

you can cheat a bit with

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

regards

chris

Re: QlikView replacing nulls with zeros in a table

salto — Thu, 12 Dec 2013 13:04:11 GMT

Hello,

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

Hope this helps.-

Re: QlikView replacing nulls with zeros in a table

Jason_Michaelides — Thu, 12 Dec 2013 13:07:55 GMT

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?

Re: QlikView replacing nulls with zeros in a table

Thu, 12 Dec 2013 13:08:25 GMT

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.

Re: QlikView replacing nulls with zeros in a table

Thu, 12 Dec 2013 13:09:13 GMT

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

Re: QlikView replacing nulls with zeros in a table

salto — Thu, 12 Dec 2013 13:11:43 GMT

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

Re: QlikView replacing nulls with zeros in a table

Thu, 12 Dec 2013 13:32:12 GMT

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

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

swuehl — Thu, 12 Dec 2013 13:43:10 GMT

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

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

Re: QlikView replacing nulls with zeros in a table

Thu, 12 Dec 2013 13:44:56 GMT

I guess in such a case sum() would be different from 0

chris

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

Fri, 13 Dec 2013 10:14:13 GMT

This worked- thanks swuehl!

thanks everyone else for all the suggestions.