topic Look up in QlikView

Look up

Fri, 18 Nov 2011 22:28:31 GMT

Hi,

I have 3 tables, TSales ,Product and SalesItem Table. Product Table serves as my look up table for my Sales table.

Sales
DepotCode
ProductCode
Quantity
SalesDate

Product (Lookup Table)
ProductName
CFTCode
Brand
Product

In this two tables Sales.ProductCode = Product.CFTCode. I want my sales table to look like this:

Sales
DepotCode
Product (from Product Table)
Quantity
SalesDate

How can I do this?

Thanks

Re: Look up

johnw — Fri, 18 Nov 2011 22:49:48 GMT

One way is to read in the product table as a mapping table, then map the ProductCode to the Product with applymap(). Something like this:

[Products]:
MAPPING LOAD CFTCode, Product
FROM wherever
;
[Sales]:
LOAD
DepotCode
,applymap('Products',ProductCode) as Product
,Quantity
,SalesDate
FROM wherever
;

Another way is a left join:

[Sales]:
LOAD
DepotCode
,ProductCode
,Quantity
,SalesDate
FROM wherever
;
LEFT JOIN (Sales)
LOAD
CFTCode as ProductCode
,Product
FROM wherever
;
DROP FIELD ProductCode
;

Another way is to just leave the tables separate, and let QlikView handle them via association:

[Sales]:
LOAD
DepotCode
,ProductCode
,Quantity
,SalesDate
FROM wherever
;
[Products]:
LOAD
CFTCode as ProductCode
,Product
FROM wherever
WHERE exists(ProductCode,CFTCode)
;

Look up

Fri, 18 Nov 2011 22:52:55 GMT

Thank you Sir John! I'll try options...