topic Re: Oracle NVL equivalent in Qlickview in QlikView

Oracle NVL equivalent in Qlickview

alan_grn — Thu, 25 Mar 2010 22:27:17 GMT

Is there an equivalent funtion to NVL in Qlikview?

Oracle NVL equivalent in Qlickview

Fri, 26 Mar 2010 09:28:18 GMT

Hi,

a way could be:

If(IsNull(CustomerField, 'NN', CustomerField)

Good luck!

Rainer

Re: Oracle NVL equivalent in Qlickview

Fri, 13 Jul 2012 13:13:15 GMT

Correct me if I'm wrong:

If(IsNull([an expression that takes 10 seconds to compute], 'NN', [an expression that takes 10 seconds to compute])

will take 20 seconds to compute: 2 times the expression that takes 10 sec.

Whereas

nvl([an expression that takes 10 seconds to compute], 'NN')

will only take 10?

Re: Oracle NVL equivalent in Qlickview

jonathandienst — Fri, 13 Jul 2012 13:16:52 GMT

If the values are numeric, then Alt() is similar to NVL:

=Alt(expression, 0)

Will return 0 if expression returns a non-numeric (including a null). Only works for numerics though.

Regards

Jonathan

Re: Oracle NVL equivalent in Qlickview

Jason_Michaelides — Fri, 13 Jul 2012 13:20:27 GMT

I'm not sure if you're right Nicolas, but you could be. If that is the case, then using MAP...USING would be better:

Map_Nulls:

MAPPING LOAD

Null(),

'NN'

Autogenerate 1;

MAP Field3 USING Map_Nulls;

Data:

LOAD
Field1,

Field2,

YourExpressionThatTakes10SecondsToLoad AS Field3,

etc

FROM....;

The MAP...USING function will replace all Null() values in Field3 at the end of script execution.

Hope this helps,

Jason

Re: Oracle NVL equivalent in Qlickview

CELAMBARASAN — Fri, 13 Jul 2012 13:27:32 GMT

http://community.qlik.com/ideas/1905

Re: Oracle NVL equivalent in Qlickview

Fri, 13 Jul 2012 13:33:44 GMT

If that is the case, then using MAP...USING would be better:

Unfortunately, when I have this kind of need, it's not in the script, but in expressions.

I often have this case, and I often deal with "heavy" expressions, that's why I think the "if" used to precalculate all statements (condition, then expression, else expression) even if some are already used in the same expression.

http://community.qlik.com/ideas/1905

I've already thumbed up for this idea long time ago

Re: Oracle NVL equivalent in Qlickview

nagaiank — Sat, 14 Jul 2012 16:23:36 GMT

NVL(expr1,expr2) function:

If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

Qlikview equivalent is:

If(IsNull(expr1),expr2,expr1)

Re: Oracle NVL equivalent in Qlickview

Jason_Michaelides — Sat, 14 Jul 2012 17:34:21 GMT

Hi Nicolas,

I guess it's unfortunate that you find you have to deal with NULL values in your front end. I learnt the hard way to try and avoid using NULLs in any way. I always remove all NULLs in the script, replacing them with something selectable, so the only NULLs I am ever left with in the UI are those created due to missing records in QV joins. If I ever need to use these "Join" NULLs (which isn't often) I would try and do something in the script (maybe another forced join) to get rid of them.

Cheers,

Jason

Re: Oracle NVL equivalent in Qlickview

Fri, 02 Jan 2015 15:37:03 GMT

Thanks for this solution. It fit my needs perfectly.

Re: Oracle NVL equivalent in Qlickview

justaqlikker — Thu, 11 Aug 2016 12:27:17 GMT

I know this is 4 years old now, but thanks Jonathan Dienst. The "Alt()" function works beautifully as an NVL() substitute.