Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
use rangesum() instead of sum()
Try replacing NVL with Alt. The alt(x,y) function will replace a non-numeric x value with y.
i try using Alt :
Sum (
Alt(
* - ( ( Alt (
* ( (Alt(MMS_TDD_LOADING_DETAIL.NR_VAT, 0) * .01) + 1),
But it doesn't work.
I don't know if there is a syntax error
The alt function cannot be used in sql statements, only in load statements and qlikview expressions.
ok. And if i want to calculate this expression in sql statement how can i do?
use preceding load
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.
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.
