topic Re: Replace Null Values in QlikView

Replace Null Values

sridhar_sigired — Sun, 26 Oct 2014 10:19:59 GMT

Dear Community,

1. To do the aggregation calculations, i want to replace null value with 0 in my field. Apart from the IF condition do we have any chance to replace? Is there any function to use?

2. Can anyone explain the use auto number function with an example.

Thanks,

Sridhar.

Re: Replace Null Values

ashfaq_haseeb — Sun, 26 Oct 2014 10:24:27 GMT

Hi Possible,

have a look on below post for handling null values.

http://community.qlik.com/docs/DOC-3155

For autonumber() check this

http://community.qlik.com/message/270262#270262

Regards

ASHFAQ

Re: Replace Null Values

maxgro — Sun, 26 Oct 2014 18:11:53 GMT

alt function

alt(null(), 0)

alt(yourfield, 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.

Re: Replace Null Values

jagan — Mon, 27 Oct 2014 04:12:44 GMT

Hi Sridhar,

Try using the Alt(), it is similar to if else.

Emp_id = 5

Alt(Emp_id, 0) - The output is 5

Emp_id = Null

Alt(Emp_id, 0) - Now the output is 0

AutoNumber() - Gives you some number for a given value, it is useful for generating ID, for example

County

India

USA

Singapore

If you use below script

LOAD

Country

AutoNumber(Country) AS ID

FROM Data;

Now your table may be like this

County, Key

India, 1000

USA, 2000

UK, 3000

Singapore, 9000

Note: Here key values may change.

Hope this helps you.

Regards,

Jagan.

Re: Replace Null Values

Mon, 27 Oct 2014 06:47:20 GMT

Hi,

Using of ALT() we will replace the null values

Re: Replace Null Values

sridhar_sigired — Mon, 27 Oct 2014 14:22:52 GMT

Thank you all for your quick reply.

Re: Replace Null Values

Mon, 27 Oct 2014 14:24:11 GMT

we can use ALT function only on the numbers