I have a requirement like this. I have a product table with SKU and 5 other attributes like type, category,etc.
I have costs for products in a separate data file which can contain any combination of the 5 attributes and a cost associated.
I need to map the combination of columns in the data file to the attributes in the Product table and allocate the costs to each SKU.
For example,
Lets assume my product table is like this (Already loaded into qvw)
SKU Category Type Brand
A Category1 Type1 Brand1
B Category1 Type2 Brand2
C Category2 Type3 Brand3
D Category2 Type3 Brand4
Now my Data file contains
Category Type Brand Cost
Category1 Type1 NULL $200
Category1 NULL Brand2 $100
Category2 Type3 NULL $150
My final table should contain this:
SKU Category Type Brand Cost
A Category1 Type1 Brand1 $200
B Category1 Type2 Brand2 $100
C Category2 Type3 Brand3 $150
D Category2 Type3 Brand4 $150
This is a tricky requirement and I am pretty new to building an ETL in QV. I have not really used dynamic queries or joins so far so any ideas which will help me solve this problem will be appreciated.
I would use a few joins to link the Cost field to either Type or Brand in your product table. I cannot see whether Type and Brand are mutually exclusive ie if you have Type, the Brand field will be NULL and vice versa. If they are mutually exclusive, then you can join the data to the product table by:
JOIN (Product) LOAD Type Cost RESIDENT Data; JOIN (Product) LOAD Brand Cost RESIDENT Data; DROP TABLE Data;
I havent tried the code yet so you prob need to test this. The end result should be your Product Table would have a new Cost field.
