Hello,
I'm going through a QV Essentials book, now on a chapter on Mapping tables.
I'm struggling to get my head around how Category_Map is loaded from a text file and then used within another Mapping table without a matching key (lookup value) which in the case of Category_Map would be ProductCategoryID, while SubCategory_CategoryMap wants to retrieve CategoryName using ProductSubcategoryID as a lookup value that doesn't exist in the Category_Map table? The Product table where the mapping tables are applied only contains ProductSubcategoryID.
Any clarification on this would be much appreciated, thanks a lot!
ProductCategory text file:
ProductCategoryID CategoryName
1 Bikes
2 Components
3 Clothing
4 Accessories
Sample from ProductSubCategory file:
| ProductSubcategoryID |
ProductCategoryID |
SubCategoryName |
| 1 |
1 |
Mountain Bikes |
| 2 |
1 |
Road Bikes |
| 3 |
1 |
Touring Bikes |
| 4 |
2 |
Handlebars |
| 5 |
2 |
Bottom Brackets |
| 6 |
2 |
Brakes |
| 7 |
2 |
Chains |
Mapping tables:
//***** Product Category Map ****//
Category_Map:
Directory;
Mapping LOAD
ProductCategoryID as CategoryID,
CategoryName as CategoryName
FROM
QlikViewEssentials\Data\ProductCategory.txt
(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);
// *** SubCategory Name Map ****//
SubCategory_Map:
Mapping LOAD
ProductSubcategoryID,
SubCategoryName;
SQL SELECT *
FROM ProductSubcategory;
// *** SubCategory Category Desc Map *** //
SubCategory_CategoryMap:
Mapping load
ProductSubcategoryID,
ApplyMap('Category_Map', ProductSubcategoryID, 'No Category'); //???????????????????????????????????????//
SQL SELECT *
FROM ProductSubcategory;
Maps applied:
Product:
LOAD ProductID,
ProductLine,
ProductName,
ProductNumber,
ProductSubcategoryID,
ReorderPoint,
SafetyStockLevel,
StandardCost,
Style,
UnitPrice as ListPrice,
if (ProductLine = 'M', 'Mountain',
if (ProductLine = 'R', 'Road',
if (ProductLine = 'S', 'Accessory', 'Components')) ) as ProductLineDesc,
ApplyMap ('SubCategory_Map', ProductSubcategoryID, 'No SubCategory') as [SubCategory Desc],
ApplyMap('SubCategory_CategoryMap', ProductSubcategoryID, 'No Category') as [Category Desc]
;
SQL SELECT *
FROM Product;
