topic Re: Loading field (from 2 tables) in SQL script in QlikView

Loading field (from 2 tables) in SQL script

Fri, 20 Mar 2015 11:32:25 GMT

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;

Re: Loading field (from 2 tables) in SQL script

Gysbert_Wassenaar — Fri, 20 Mar 2015 11:40:58 GMT

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;

Re: Loading field (from 2 tables) in SQL script

MK_QSL — Fri, 20 Mar 2015 11:41:50 GMT

either use this expression at UI/Chart level

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

Re: Loading field (from 2 tables) in SQL script

Fri, 20 Mar 2015 11:49:13 GMT

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