Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
aboumejjane
Contributor

lost in translation

Hi There,

I'm following the Qlikview "Get started: Developers Course" I'm a bit lost in translation.

I'm trying to translate the following code/script:

UnitPrice*Quantity*(1-Discount) AS Sales

OrderDetails:

LOAD Discount,

    LineNo,

    OrderID,

    ProductID,

    Quantity,

    UnitPrice;

SQL SELECT *

FROM `Order Details`;

There is no explanation given in the course what this means and why, therefore i'm trying to use my own interpatation of the script. What it says is: Load field "UnitPrice" multiply by Quantity minus 1 discount and named it as Sales. It's properbly wrong But can someone help me translate it to normal English.

Thanks in advance!!

Isam, a qlikview newbie

1 Solution

Accepted Solutions

Re: lost in translation

Well, I don't know the formula because I don't know the data source, but it seems to me the Discount field stores a percentage using decimals, so if you sell 10 quantity at a unit price of 5 and you want the 20% discount that's equal to

UnitPrice * Quantity * (1 - Discount) AS Sales

5         * 10       * (1 - 0,2)      = 40 (actual sales)

I maybe wrong, I'm not specially good at maths but it does make a lot of sense.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

6 Replies

Re: lost in translation

Isam,

I don't thing it's wrong. That means that you can create a new field in the LOAD part following those instructions so the script results in

OrderDetails:

LOAD Discount,

    LineNo,

    OrderID,

    ProductID,

    Quantity,

    UnitPrice,

    UnitPrice * Quantity * (1 - Discount) AS Sales; // New field

SQL SELECT *

FROM `Order Details`;

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

aboumejjane
Contributor

Re: lost in translation

Hi thanks for your reply. I know that Qlikview makes a new field and names it "Sales" . But what's in the Sales field? Is it a calculation? if so, what is Qlikview calculating?

Thanks

Re: lost in translation

Well, that's the point: For each record QlikView pulls it will take the values from fields UnitPrice, Quantity and Discount from them and will evaluate them as you have specified and will store the result of that calculation into a new field called Sales in the same record.

Obviously is a calculated field, since it's not as it comes from the data source.

Regards,

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

aboumejjane
Contributor

lost in translation

Thanks for your explanation! I'm almost there. Why is it that Discount is placed onder bracket (1-Discount) and why is number 1 placed before Discount?

Thanks!

iSam

Re: lost in translation

Well, I don't know the formula because I don't know the data source, but it seems to me the Discount field stores a percentage using decimals, so if you sell 10 quantity at a unit price of 5 and you want the 20% discount that's equal to

UnitPrice * Quantity * (1 - Discount) AS Sales

5         * 10       * (1 - 0,2)      = 40 (actual sales)

I maybe wrong, I'm not specially good at maths but it does make a lot of sense.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

aboumejjane
Contributor

Re: lost in translation

Thanks for your help!!! It really does make sence now.

Community Browser