Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
flygstolen_fred
Creator
Creator

Calculation in load script

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;

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

3 Replies
YoussefBelloum
Champion
Champion

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

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
Creator
Creator
Author

Thanks Sunny, works great!