topic Re: Expression in QlikView

Expression

priyarane — Mon, 05 Oct 2015 16:05:22 GMT

Hi Community,

What is the expression in QV for below

Sum(outstanding) where nvl(bs_type, 'ASS') != 'OFB'

Re: Expression

Peter_Cammaert — Mon, 05 Oct 2015 16:23:48 GMT

Where does this come from? Meaning what DB uses expressions like this?

Peter

Re: Expression

MK_QSL — Mon, 05 Oct 2015 16:25:07 GMT

U can use Resident Load with Desc order of certain field and use Peek or Previous in script

Re: Expression

malini_qlikview — Mon, 05 Oct 2015 16:25:35 GMT

you can use null handling function in the script as below and use set analysis in your expression

NullAsValue bs_type;

Set NullValue = 'ASS';

Expression : =Sum({<bs_type-={'OFB'}>}outstanding)

Re: Expression

jduarte12 — Mon, 05 Oct 2015 16:27:56 GMT

Hello,

It's not totally clear to me the meaning of your expression. What is nvl(bs_type, 'ASS') ?

Nevertheless, if an if statement does not fulfill your needs, I would suggest a look on Set Analysis.

Regards,

João Duarte

Re: Expression

hic — Mon, 05 Oct 2015 16:28:17 GMT

If you want to make a chart with Set Analysis, you could use

Sum({$<bs_type -= {'OFB'}>} outstanding)

but this does not do exactly what you want: You will have a difference when bs_type is NULL.

To fix this you need to remove NULLs in the script:

If(IsNull(bs_type),'ASS',bs_type) as bs_type

or you can use advanced set analysis on the primary key in the table:

Sum({$<key = E({$<bs_type={'OFB'}>} key)>} outstanding)

In other words: Find the values of key that get excluded at a selection of 'OFB' and use these as aggregation scope.

HIC

Re: Expression

Anonymous — Mon, 05 Oct 2015 16:28:38 GMT

Hi Priya,

I Think its Oracle DB.

Script:

Load *,

if(isnull(bs_type),'ASS',bs_type) as bs_type

Resident ......;

UI:

Sum({<bs_type -= {'OFB'}>}outstanding)

Re: Expression

priyarane — Mon, 05 Oct 2015 16:28:40 GMT

Is it possible to write expression in Chart?

Re: Expression

Peter_Cammaert — Mon, 05 Oct 2015 16:29:34 GMT

Found it. The answer is: it depends. If you want to use comparable code in QV script, you can use something like:

Sum(if (bs_type <> 'OFB', outstanding)) AS ...

In many cases, NULL values don't need any special treatment in QlikView.

But this may not be of much use as the context will dictate a correct analogy. Can you elaborate about this a bit?

Peter

Re: Expression

Peter_Cammaert — Mon, 05 Oct 2015 16:30:58 GMT

Yes. Like this.

=Sum(if (bs_type <> 'OFB', outstanding))

But again, alternatives like set analysis may provide a better fit (and better performance)