Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

NVL QLIKVIEW

Hi all,

i have this expression:


SUM (

      NVL (

          (  ( (MMS_TDD_LOADING_DETAIL.NR_VENDOR_AMOUNT) * MMS_TDD_LOADING_DETAIL.NR_PRICE)

            * - (  (  NVL (

                            (  (MMS_TDD_LOADING_DETAIL.NR_DISCOUNT * MMS_TDD_LOADING_DETAIL.NR_DISCOUNTABLE)

                                            + (  MMS_TDD_LOADING_DETAIL.NR_DISCOUNT

                                               -   ( (  MMS_TDD_LOADING_DETAIL.NR_DISCOUNT

                                                      * (MMS_TDD_LOADING.NR_DISCOUNT / 100)))

                                                 * MMS_TDD_LOADING_DETAIL.NR_DISCOUNTABLE)),

                                           0)

                                      * .01)

                                   - 1))

        * ( (NVL (MMS_TDD_LOADING_DETAIL.NR_VAT, 0) * .01) + 1),

                           0)) as "LOCAL_AMOUNT"

In qlikview doesn't work because NVL function.

How can i transform this expression so it can work?

Thanks a lot.

8 Replies
luciancotea
Specialist
Specialist

use rangesum() instead of sum()

Gysbert_Wassenaar

Try replacing NVL with Alt. The alt(x,y) function will replace a non-numeric x value with y.


talk is cheap, supply exceeds demand
Not applicable
Author

i try using Alt :

Sum (

      Alt(

          (  ( (MMS_TDD_LOADING_DETAIL.NR_VENDOR_AMOUNT) * MMS_TDD_LOADING_DETAIL.NR_PRICE)

            * - (  (  Alt (

                            (  (MMS_TDD_LOADING_DETAIL.NR_DISCOUNT * MMS_TDD_LOADING_DETAIL.NR_DISCOUNTABLE)

                                            + (  MMS_TDD_LOADING_DETAIL.NR_DISCOUNT

                                               -   ( (  MMS_TDD_LOADING_DETAIL.NR_DISCOUNT

                                                      * (MMS_TDD_LOADING.NR_DISCOUNT / 100)))

                                                 * MMS_TDD_LOADING_DETAIL.NR_DISCOUNTABLE)),

                                           0)

                                      * .01)

                                   - 1))

                           * ( (Alt(MMS_TDD_LOADING_DETAIL.NR_VAT, 0) * .01) + 1),

                           0)) as "LOCAL_AMOUNT"

But it doesn't work.

I don't know if there is a syntax error

Gysbert_Wassenaar

The alt function cannot be used in sql statements, only in load statements and qlikview expressions.


talk is cheap, supply exceeds demand
Not applicable
Author

ok. And if i want to calculate this expression in sql statement how can i do?

Anonymous
Not applicable
Author

use preceding load

luciancotea
Specialist
Specialist

Then you use NVL() because it's a SQL function.

In QlikView, you use rangesum() which treats all nonnumeric as zero. Or alt(), if you need a different default value.

Gysbert_Wassenaar

Use the NVL function if your source database is an Oracle database. If you use a database from another vendor then read the relevant documentation or ask your local friendly database administrator.


talk is cheap, supply exceeds demand