Problem adding a new key

[egoziyan]

G'day

I have a sales model that have all my data in it and it's working fine.

I want to add to it new data such as current inventory and entry qty for each product_id.

At the moment I am using the Fallowing KEY

My problem is when adding the Product_id from the product table to the KEY because the product table doesn't have SA_WHS or SA_DATE which I use in the KEY table.

KEY3:
LOAD DISTINCT
     SA_SALESMAN,
     SA_WHS
FROM
$(QVD_PATH)SALES_TMP2.QVD(qvd);
STORE KEY3 INTO $(QVD_PATH)KEY3.QVD;
DROP TABLE KEY3;

KEY2:
LOAD DISTINCT
     MONTHSTART(SA_DATE) AS SA_DATE
FROM
$(QVD_PATH)targets_tmp2.qvd(qvd);
JOIN
LOAD DISTINCT
     SA_SALESMAN,
     SA_WHS
FROM
$(QVD_PATH)KEY3.QVD(qvd);
STORE KEY2 INTO $(QVD_PATH)KEY2.QVD;
DROP TABLE KEY2;

KEY1:
LOAD DISTINCT
     SA_DATE,
     SA_SALESMAN,
     SA_WHS
FROM
$(QVD_PATH)KEY2.QVD(qvd);
JOIN
LOAD  DISTINCT
SA_WHS,  
     SA_SALESMAN,
     SA_DATE
FROM
$(QVD_PATH)SALES_TMP2.QVD
(qvd);
JOIN
LOAD DISTINCT
    SA_DATE,
     SA_WHS
FROM
$(QVD_PATH)targets_tmp2.qvd
(qvd);

KEY:
LOAD
SA_SALESMAN,
     SA_DATE,
     SA_WHS,
        SA_WHS&'-'&SA_SALESMAN&'-'&SA_DATE as SALES_KEY, 
     SA_DATE&'_'&SA_WHS AS TARGET_KEY  
RESIDENT KEY1;
DROP TABLE KEY1;

[#2]

In Table KEY2,How u r joining two QVDs ?

Problem is not adding new key..problem is in ur data model.

Pls share some data..i will try.

regards-bika

[egoziyan]

Thanks bika

I am adding my data model with minimum data so you can look at the script.

Tha model is working great for a few years and now I am trying to add new tables (inventory and entry data) to it.

Thanks in advnace

Yaniv

[egoziyan]

Thanks er

I am trying to add the product_id field from the product table to the Key table and in your qvw I see that you took a diffrent approace to arrange key1 but without the product_id.