Discussion Board for collaboration related to QlikView App Development.
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
Anyone having any clue?
Can you post sample script, to check the issues in your loop?
Did you created the Concatenation like this?
Load max(Product_Id) as MaxId
Let MaxId = peek('MaxId',-1);
drop table Stored_Products;
LOAD Product_Id,Product_Line, Product_category,Product_Subcategory
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
where Product_Id > $(MaxId);
LOAD Product_Id,Product_Line, Product_category,
store NewProducts into (qvd);
please show the data model as well.
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
KEY_DATE_SECID AS KEY_DATE_PRODID,
where SALES_DATE >= date(Date#('$(vStartDate)','DD-MMM-YYYY')) AND SALES_DATE < date(Date#('$(vEndDate)','DD-MMM-YYYY'));
INNER JOIN TMP_Filtered_PROD_TMP:
where SALES_DATE >= date(Date#('$(vStartDate)','DD-MMM-YYYY')) AND SALES_DATE <= date(Date#('$(vEndDate)','DD-MMM-YYYY'));
DROP TABLE TMP_Filtered_PROD_TMP;
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