Loading field (from 2 tables) in SQL script

[#1]

Hi,

I would like to add the following field to my script ( LOAD *, (([Unitprice]-[UnitCost])*[Quantity])*(1-[Discount]) as Revenue;)

. Where should I add it? (it needs to take columns from 2 tables). See the script below.

Thank you in advance.

SQL SELECT Address,

    City,

    CompanyName,

    ContactName,

    Country,

    CustomerID,

    DivisionID,

    Fax,

    Phone,

    PostalCode,

    StateProvince

FROM Customers;

SQL SELECT CategoryID,

    ProductID,

    ProductName,

    QuantityPerUnit,

    SupplierID,

    UnitCost,

//    UnitPrice Already in other table

    UnitsInStock,

    UnitsOnOrder

FROM Products;

LOAD *,

     Year(OrderDate) as Year,

     'Q' & ceil(Month(OrderDate)/3) as Quarter,

     Month(OrderDate) as Month;

SQL SELECT CustomerID,

    EmployeeID,

    Freight,

    OrderID,

    ShipperID,

    OrderDate

FROM Orders;

SQL SELECT Discount,

    LineNo,

    OrderID,

    ProductID,

    Quantity,

  Unitprice,

  [unitprice]*[quantity] as [Gross Sales],

  ([Unitprice]*[Quantity])*(1-[Discount]) as [Net Sales]

FROM `Order Details`;

SQL SELECT DivisionID,

  DivisionName

From Divisions;

[Gysbert_Wassenaar]

Perhaps like this:

SQL SELECT Address,

    City,

    CompanyName,

    ContactName,

    Country,

    CustomerID,

    DivisionID,

    Fax,

    Phone,

    PostalCode,

    StateProvince

FROM Customers;

SQL SELECT CategoryID,

    ProductID,

    ProductName,

    QuantityPerUnit,

    SupplierID,

    UnitCost,

//    UnitPrice Already in other table

    UnitsInStock,

    UnitsOnOrder

FROM Products;

MapUC:

Mapping LOAD ProductID, UnitCost

RESIDENT Products;

LOAD *,

     Year(OrderDate) as Year,

     'Q' & ceil(Month(OrderDate)/3) as Quarter,

     Month(OrderDate) as Month;

SQL SELECT CustomerID,

    EmployeeID,

    Freight,

    OrderID,

    ShipperID,

    OrderDate

FROM Orders;

OrderDetails:

LOAD *, ([Unitprice]-applymap('MapUC',ProductID))*[Quantity])*(1-[Discount]) as Revenue;

SQL SELECT Discount,

    LineNo,

    OrderID,

    ProductID,

    Quantity,

  Unitprice,

  [unitprice]*[Quantity] as [Gross Sales],

  ([Unitprice]*[Quantity])*(1-[Discount]) as [Net Sales]

FROM `Order Details`;

SQL SELECT DivisionID,

  DivisionName

From Divisions;

[MK_QSL]

either use this expression at UI/Chart level

or

you need to join the tables in script before using them...

[#4]

Hi ,

Firstly store all raw table into qvds.

then create a fact table by using left join (orders,orders detail,products)  and link master table (customer)

FACTTABLE:

LOAD CustomerID,

    EmployeeID,

    Freight,

    OrderID,  //--- Key for left join to Order Details Table

    ShipperID,

    OrderDate,

     Year(OrderDate) as Year,

     'Q' & ceil(Month(OrderDate)/3) as Quarter,

     Month(OrderDate) as Month

FROM Orders;

Left Join

LOAD Discount,

    LineNo,

    OrderID,//--- Key for left join to Orders Table

    ProductID,//--- Key for left join to Products Table

    Quantity,

  Unitprice,

  [unitprice]*[quantity] as [Gross Sales],

  ([Unitprice]*[Quantity])*(1-[Discount]) as [Net Sales]

FROM Order Details;

Left Join

LOAD CategoryID,

    ProductID,//--- Key for left join to Orders Table

    ProductName,

    QuantityPerUnit,

    SupplierID,

    UnitCost,

    UnitsInStock,

    UnitsOnOrder

FROM Products;

FACTTABLE_MAIN:

LOAD *,

(([Unitprice]-[UnitCost])*[Quantity])*(1-[Discount]) as Revenue

Resident FACTTABLE;

DROP Table FACTTABLE;

Custmer:

LOAD Address,

    City,

    CompanyName,

    ContactName,

    Country,

    CustomerID, //Link with FACTTABLE_MAIN

    DivisionID,

    Fax,

    Phone,

    PostalCode,

    StateProvince

FROM Customers;

Regards

Vimlesh