Restrictions to LOOKUPVALUE

[aiplbonn]

Hi,

to calculate the tax (mwst) in an order table, i have created a tax (mwst) table with the matching country code.

MWST = LOOKUPVALUE(Mwst[VAT Regular],MWST[land],OrderData[land],1)

But now I need another restriction.

If the field class in the table OrderData contains the word "special", the field "VAT Special" should be used in the VAT table instead of the field VAT Regular.
If the field class contains something other than "special", then simply "VAT Regular".

In Speudo code like this.

if(OrderData[class]= special)

         LOOKUPVALUE(Mwst[VAT Special],MWST[land],OrderData[land],1)

else

        LOOKUPVALUE(Mwst[VAT Regular],MWST[land],OrderData[land],1)

But i have no idea how to do this the rigth way.

[FreemanZ · Accepted solution]

hi @aiplbonn 

try like:

MWST  =

VAR _special =        

LOOKUPVALUE(

     Mwst[VAT Special],

     MWST[land],

     OrderData[land],

    1 

)

VAR _regular = 

LOOKUPVALUE(

    Mwst[VAT Regular],

    MWST[land],

    OrderData[land],

    1

)

RETURN 

IF(

    CONTAINSSTRING(OrderData[class], "special"),

    _special, _regular

) 

[aiplbonn · Accepted solution]

hi @FreemanZ 

this works perfectly! Thank you so much!

[FreemanZ · Accepted solution]

hi @aiplbonn 

try like:

MWST  =

VAR _special =        

LOOKUPVALUE(

     Mwst[VAT Special],

     MWST[land],

     OrderData[land],

    1 

)

VAR _regular = 

LOOKUPVALUE(

    Mwst[VAT Regular],

    MWST[land],

    OrderData[land],

    1

)

RETURN 

IF(

    CONTAINSSTRING(OrderData[class], "special"),

    _special, _regular

)