Calculation in load script

[flygstolen_fred]

Hi,

In our database all products sold have a price, cost, units and VAT amount.

The VAT amount is the total VAT amount for all units sold for that certain product.

Now I wanna add the right portion of VAT in the load script. Is it possible to add this in a load statement?

I wanna calculate the VAT percentage and the add that to both price and cost.

I want to do something like this (written in c# as an example):

If (VatAmount > 0) {

     var vatPercentage = ((VatAmount / Units) / Price) + 1; //Get VAT percentage

     Price = Price * vatPercentage;

     Cost = Cost * vatPercentage;

}

Product:

LOAD

    Code,

    Description,

    VatAmount,

    Units,

    Price,

    Cost;

[YoussefBelloum]

Hi,

Product:

LOAD

    Code,

    Description,

    VatAmount,

    if(VatAmount>0, ((VatAmount / Units) / Price) + 1,1) as vatPercentage, 

    Price * vatPercentage as Final_VAT_amount,

    Cost * vatPercentage as Final_VAT_cost,

    Units,

    Price,

    Cost

FROM TABLE...;

I just put 1 on the ELSE to have a minimum of 1% for the VAT %

Try this, just check your calcualations Rules..

Hope it helps

YB

[sunny_talwar · Accepted solution]

May be this

Product:

LOAD *,

     Price * vatPercentage as New_Price,

     Cost * vatPercentage as New_Cost;

LOAD *,

     ((VatAmount / Units) / Price) + 1 as vatPercentage;

LOAD

    Code,

    Description,

    VatAmount,

    Units,

    Price,

    Cost;

[flygstolen_fred]

Thanks Sunny, works great!