Replace Null Values

[sridhar_sigired]

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.

[ashfaq_haseeb]

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

[maxgro]

1)

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.

[jagan · Accepted solution]

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

UK

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.

[#5]

Hi,

Using of ALT() we will replace the null values

[sridhar_sigired]

Thank you all for your quick reply.

[#7]

we can use ALT function only on the numbers