Team Please Help me out in this!

[#1]

//**************Products Table*************

Products:

LOAD CategoryID,

    ProductID,

    ProductName,

    QuantityPerUnit,

    SupplierID,

    UnitCost,

    //ApplyMap('Quantity_Map', Quantity,'') as Qnty,

    UnitsInStock,

    UnitsOnOrder;

SQL SELECT *

FROM Products;

UnitCost_Mapping:

Mapping Load ProductID,

UnitCost;

SQL Select * From Products;

//**************Order Details Table*****************

OrderDetails:

Load LineSalesAmount - CostOfGoodsSold as Margin, *;

LOAD Discount,

    LineNo,

    OrderID,

    autoNumber (OrderID & '|' & LineNo) as OrderLineKey,  

    ProductID,

    ProductID as ProductIDRecordCounter,

    Quantity,

    UnitPrice,

    UnitPrice*Quantity*(1-Discount) as LineSalesAmount,

    ApplyMap('UnitCost_Mapping', ProductID, 1)*Quantity as CostOfGoodsSold;

SQL SELECT *

FROM `Order Details`;

Left Join (Orders)

LOAD OrderID,

sum(LineSalesAmount) as OrderSalesAmount Resident OrderDetails Group by OrderID;

I am Not Able to get CostOfGoodsSold

[#2]

By Using Applymap function please help me out

[maxgro]

do you get an error?

or what do you get in CostOfGoodsSold?

try do add this and let we know what you get ApplyMap('UnitCost_Mapping', ProductID)

or post your Qlik doc

or create a straight table chart with

dimensions OrderLineKey, ProductID, UnitCosts

expressions sum(Quantity) and sum(Quantity*UnitCosts)

[Joseph_Musekura]

Hi,

Try the following (changes made are in highlighted in yellow color):

1. I commented "orders" because I don’t see where it comes from
2. I separated calculation and apply mapping

//**************Products Table*************

Products:

LOAD CategoryID,

ProductID,

ProductName,

QuantityPerUnit,

SupplierID,

UnitCost,

//ApplyMap('Quantity_Map', Quantity,'') as Qnty,

UnitsInStock,

UnitsOnOrder;

SQL SELECT *

  FROM Products;

UnitCost_Mapping:

Mapping Load  ProductID,

               UnitCost;

SQL Select * From
Products;

//**************Order Details Table*****************

OrderDetails:

Load
LineSalesAmount - CostOfGoodsSold as Margin, *;

Load *,

    myUnitCost* Quantity as CostOfGoodsSold;

LOAD Discount,

LineNo,

OrderID,

autoNumber (OrderID & '|' & LineNo) as OrderLineKey,

ProductID,

ProductID as
ProductIDRecordCounter,

Quantity,

UnitPrice,

UnitPrice*Quantity*(1-Discount) as LineSalesAmount,

ApplyMap('UnitCost_Mapping', ProductID, 1) as myUnitCost;

SQL SELECT *

FROM `Order

Details`;

//Left Join (Orders)

  //LOAD OrderID,

  //sum(LineSalesAmount) as OrderSalesAmount Resident
OrderDetails Group by OrderID;

[#5]

Hi Massimo

thank you very much for your kind reply, here the link for the document

kindly have a look and help me.

Regards

Chandan

[#6]

Hi Joseph

Thank you very much for your kind reply, i tried but the problem could not solved here i am adding a link of my document kindly go through it once and help me

test.qvw

Regards

Chandan

[maxgro]

Products:

LOAD CategoryID,

    ProductID,

    ProductName,

    QuantityPerUnit,

    SupplierID,

    UnitCost,

    UnitsInStock,

    UnitsOnOrder;

SQL Select * From Products;

UnitCost_Mapping:

Mapping Load ProductID, UnitCost

Resident Products;

OrderDetails:

Load LineSalesAmount-CostOfGoodsSold as Margin, *;

LOAD Discount,

    LineNo,

    OrderID,

    autoNumber (OrderID & '|' & LineNo) as OrderLineKey,  

    ProductID,

    ProductID as ProductIDRecordCounter,

    Quantity,

    UnitPrice,

    UnitPrice*Quantity*(1-Discount) as LineSalesAmount,

    ApplyMap('UnitCost_Mapping', ProductID)*Quantity as CostOfGoodsSold;

SQL SELECT * FROM `Order Details`;

[#8]

Hi Massimo

Thank you very much for your kind reply but even though i am getting the nothing with this in costofgoodssold

[maxgro · Accepted solution]

a bit strange as I tried starting (see first line in my qlik doc) from your qlik doc and I get costofgoodssold

in the attachement my attempt

- with applymap in the script

- with an expression in the charts (no need for applymap)

[#10]

Hi Massimo

Thank you very much for your kind help i got the answer.

Regards

Chandan