Expression

[priyarane]

Hi Community,

What is the expression in QV for below

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

[Peter_Cammaert]

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

Peter

[MK_QSL]

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

[malini_qlikview]

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)

[jduarte12]

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

[hic · Accepted solution]

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

[Anonymous]

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)

[priyarane]

Is it possible to write expression in Chart?

[Peter_Cammaert]

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

[Peter_Cammaert]

Yes. Like this.

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

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