ETL question

Tue, 02 Mar 2010 10:15:15 GMT

Hi all,

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.

Thanks

Arun

ETL question

Tue, 02 Mar 2010 11:01:14 GMT

Hej Arun

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.

Hope that helps.

topic ETL question in QlikView

ETL question

ETL question