topic Re: Filling missing value with number 0 in QlikView

Filling missing value with number 0

Tue, 31 Dec 2013 15:28:59 GMT

Hi guys,

I m creating a report where there are many missing values...for instance...

if you look at first line then there is a missing value for cust4 because there is no row for account "Advertising and Promotion" for cust4.

My requirement is to fill these cells with number 0.

I tried "Populate missing cells" but it has two problems...first it perhaps does not enter a number 0 and second it fills all other misisng cells in different columns also with 0 which is not desirable...

Is there any other way to accomplish my task?

Regards,

Saurabh

Re: Filling missing value with number 0

Anonymous — Tue, 31 Dec 2013 15:32:21 GMT

Ramya

In your expression could you do something like :

if ( isnull ( [YourField] ) , 0 , sum ( [YourField] )

Best Regards, Bill

Re: Filling missing value with number 0

Tue, 31 Dec 2013 15:34:57 GMT

i did this

sum(if isnull([Field]), 0, Field)

and it did not work....

Re: Filling missing value with number 0

Nicole-Smith — Tue, 31 Dec 2013 15:35:50 GMT

alt(YourExpression, 0)

alt(case1[ , case2 , case3 , ...] , else)

The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

Example:

alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' )

Will test if the field date contains a date according to any of the three specified date formats. If so, it will return the original string and a valid number representation of a date. If no match is found, the text 'No valid date' will be returned (without any valid number representation).

Re: Filling missing value with number 0

Clever_Anjos — Tue, 31 Dec 2013 15:42:07 GMT

alt(sum(yourfield),0) and fill Null Symbol and Missing symbol with "0.00"

Re: Filling missing value with number 0

Tue, 31 Dec 2013 15:42:48 GMT

So for my purpose...

it might look like

alt(Not IsNull(Amount), 0)...

I tried but it gave some weird results....positive amounts became negative...

let me try few more options and i will update you.

Re: Filling missing value with number 0

Nicole-Smith — Tue, 31 Dec 2013 15:43:19 GMT

alt(Amount, 0)

Re: Filling missing value with number 0

Tue, 31 Dec 2013 15:43:20 GMT

let me try this.

Re: Filling missing value with number 0

Tue, 31 Dec 2013 15:44:54 GMT

nope, it is not working...

but let me try few other options of expressions...

Re: Re: Filling missing value with number 0

Anonymous — Tue, 31 Dec 2013 15:54:56 GMT

Ramya

The alt() that has been suggested by other above worked fine when I tested it, see attached qvw.

Best Regards, Bill

Re: Filling missing value with number 0

Tue, 31 Dec 2013 15:58:29 GMT

Thanks Bill,

I see it is working....may be something else wrong with my expression...i am trying with it...and will update you guys once i am done.

Re: Re: Filling missing value with number 0

Anonymous — Tue, 31 Dec 2013 16:01:15 GMT

Ramya

I have also just tested the if() variant and that works as well.

Best Regards, Bill