topic Re: Unable to resolve Synthetic key issue in QlikView

Unable to resolve Synthetic key issue

swatiras — Tue, 31 Jul 2018 14:36:54 GMT

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

Re: Unable to resolve Synthetic key issue

swatiras — Wed, 01 Aug 2018 05:53:33 GMT

Anyone having any clue?

Re: Unable to resolve Synthetic key issue

BalaBhaskar_Qlik — Wed, 01 Aug 2018 09:29:56 GMT

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);

Re: Unable to resolve Synthetic key issue

dsharmaqv — Wed, 01 Aug 2018 09:36:04 GMT

please show the data model as well.

Re: Unable to resolve Synthetic key issue

swatiras — Thu, 02 Aug 2018 08:26:02 GMT

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