Unable to remove synthetic keys

swatiras — Tue, 31 Jul 2018 09:31:28 GMT

Hi folks ,

I have a scenario in which i need to aggregate monthly numbers.

Lets say i have daily product sales, i want 1 row per month per product

This is what i am doing :

I am using a for loop to run 7 times (once each month from Jan to July)

In a TMP_TABLE , i read data from start date to end date ( for e.g Jan 01,2018- Jan 31,2018)

Then in MAIN_TABLE , i aggregate it , doing an order by.

I have these keys in main table:

1.KEY_DATE_PRODUCT_ID -> e.g JAN_2018_1006 (1006 is my product id)

2.PRODUCT_ID -> 1006

3.DATE_MONTH ->Jan

4.DATE_YEAR ->2018

5.DATE_MONTHYEAR ->Jan-2018

Apart from this i have an aggregate Sales Column SUM(SALES)

When this loop runs 7 times , i get 7 tables created in my data model with all above 5 keys in synthetic table.

Hence in the end there is a single main table , with all the rows duplicated.

How can i avoid it ?

Re: Unable to remove synthetic keys

petter — Tue, 31 Jul 2018 15:59:01 GMT

You probably don't need to loop at all. You can read all the months at once unless they come from a database where the months data come from different tables or if they come from files and reside in different folders. Why do you need to use an ORDER BY and a temporary table? Qlik index all fields at the end of the load script execution anyway. Sometimes ORDER BY is necessary for some inter-record functions with logic to work correctly - which is one of the main reasons I use ORDER BY.

Even if you have to do looping it shouldn't be a problem with synthetic keys except that you make sure you don't repeat the same fields unnecessary in different tables that already have a key on a date.

In your case I guess it could look something like this:

DETAILS:

LOAD * INLINE [PRODUCT_ID]; // Create an empty table with just one field.

FOR t=1 TO 7

CONCATENATE LOAD

KEY_DATE_PRODUCT_ID,

PRODUCT_ID,

DATE_MONTH ,

DATE_YEAR,

DATE_MONTHYEAR,

....

FROM

[...........];

MAIN:

LOAD

KEY_DATE_PRODUCT_ID, // This is the only key you need - will avoid synthetic

Sum(SALES) AS SumSales

RESIDENT

DETAILS

GROUP BY

KEY_DATE_PRODUCT_ID;

Lastly I can't see much benefit of doing an aggregation in the load script as it is just as easy to do it in you UI directly in the charts and tables...

topic Unable to remove synthetic keys in QlikView

Unable to remove synthetic keys

Re: Unable to remove synthetic keys