Unable to remove synthetic keys

[swatiras]

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 ?

[petter]

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

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

NEXT

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...