Unable to resolve Synthetic key issue

[swatiras]

Hi folks ,

I have  a situation where i need to perform monthly aggregation for product sales and some other aggregations. This is what I have done.

There is a file which contains daily sales data from starting Jan 01,2018

I have used a looping logic to run 7 times ( Once for each month starting Jan-Jul)

So i have a TMP_TABLE which will read data starting Jan 1- Jan 31

MAIN_TABLE will aggregate using group by

Here are the keys I have

1. KEY_DATE_PRODUCT_ID     -> e.g Jan-2018_1006 ( 1006 is my product_id)

2. Date_Month                              ->Jan

3. Date_Year                                 ->2018

4.Date_MonthYear                         -> Jan-2018

5. PRODUCT_ID                             -> 1006

6th column lets say is SUM_SALES

Now since this loop runs 7 times, each time monthly data is read into TMP_TABLE , then in MAIN_TABLE  i aggregate using resident load on TMP_TABLE and then drop TMP_TABLE

Now I am getting a synthetic table/key created on all the above columns, and eventually the final result i have contains data only for January

Please help. How to i get rid of synthetic table? The intent is to have monthly aggregated data all in a single table MAIN_TABLE

[swatiras]

Anyone having any clue?

[balabhaskarqlik]

Can you post sample script, to check the issues in your loop?

Did you created the Concatenation like this?

Stored_Products:

LOAD Product_Id,

     Product_Line,

     Product_category,

     Product_Subcategory

FROM (qvd);

Max_Product_ID:

Load max(Product_Id) as MaxId

resident Stored_Products;

Let MaxId = peek('MaxId',-1);

drop table Stored_Products;

NewProducts:

LOAD Product_Id,Product_Line, Product_category,Product_Subcategory

     from

     (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

     where Product_Id > $(MaxId);

Concatenate

LOAD Product_Id,Product_Line, Product_category,

     Product_Subcategory

FROM (qvd);

store NewProducts into (qvd);

[dsharmaqv]

please show the data model as well.

[swatiras]

Here is the code

// Loop for month wise aggregation

For k= 0 to 7 step 1;

LET vStartDate=Date(MakeDate(vYear, $(k), '01'), 'DD-MMM-YYYY');

LET vEndDateStart=Date(MakeDate(vYear, k, 1), 'DD-MMM-YYYY'); //vYear here is 2018

LET vEndDate=Date(Floor(MonthEnd(vEndDateStart)),'DD-MMM-YYYY');

TMP_Filtered_PROD_TMP:

LOAD

KEY_DATE_SECID AS KEY_DATE_PRODID,

SALES_DATE ,

PRODUCT_ID,

PRODUCT_SALES

FROM $(varQVDStoreLocation)QVD_TAO_PRODUCT_2018.qvd(qvd)

where SALES_DATE >= date(Date#('$(vStartDate)','DD-MMM-YYYY')) AND  SALES_DATE < date(Date#('$(vEndDate)','DD-MMM-YYYY'));

INNER JOIN TMP_Filtered_PROD_TMP:

LOAD

KEY_DATE_PRODID,

PRODUCT_NAME,

PRODUCT_QUANTITY,     

PRODUCT_REVENUE

FROM $(varQVDStoreLocation)QVD_TAO_PRODUCT_DETAILS.qvd(qvd)

where SALES_DATE >= date(Date#('$(vStartDate)','DD-MMM-YYYY')) AND  SALES_DATE <= date(Date#('$(vEndDate)','DD-MMM-YYYY'));

TMP_Filtered_PROD:

NoConcatenate

LOAD

KEY_DATE_PRODID,

      SALES_DATE,

      PRODUCT_ID,

      PRODUCT_SALES,

      PRODUCT_NAME,     

      PRODUCT_QUANTITY,     

       PRODUCT_REVENUE

      RESIDENT TMP_Filtered_PROD_TMP;

DROP TABLE TMP_Filtered_PROD_TMP;

NEXT;

In the datamodel , i see 7 different instances of TMP_Filtered_PROD, and there is a synthetic table with all the columns in TMP_Filtered_PROD

Please help