Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
swatiras
Contributor
Contributor

Unable to resolve Synthetic key issue

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

4 Replies
swatiras
Contributor
Contributor
Author

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
Creator III
Creator III

please show the data model as well.

swatiras
Contributor
Contributor
Author

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