Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Filling missing value with number 0

Hi guys,

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

Screen Shot 2013-12-31 at 7.24.05 AM.png

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

Tags (3)
1 Solution

Accepted Solutions

Re: Filling missing value with number 0

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

View solution in original post

11 Replies

Re: Filling missing value with number 0

Ramya

In your expression could you do something like :

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

Best Regards,     Bill

Not applicable

Re: Filling missing value with number 0

i did this

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

and it did not work....

Re: Filling missing value with number 0

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

View solution in original post

Employee
Employee

Re: Filling missing value with number 0

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

Not applicable

Re: Filling missing value with number 0

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

alt(Amount, 0)

Not applicable

Re: Filling missing value with number 0

let me try this.

Not applicable

Re: Filling missing value with number 0

nope, it is not working...

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

Re: Re: Filling missing value with number 0

Ramya

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

Best Regards,     Bill