Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
sridhar_sigired
New Contributor III

Replace Null Values

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.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Replace Null Values

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.

6 Replies

Re: Replace Null Values

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

MVP
MVP

Re: Replace Null Values

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.

MVP
MVP

Re: Replace Null Values

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.

Not applicable

Re: Replace Null Values

Hi,

Using of ALT() we will replace the null values

sridhar_sigired
New Contributor III

Re: Replace Null Values

Thank you all for your quick reply.

Not applicable

Re: Replace Null Values

we can use ALT function only on the numbers

Community Browser