Am want to sort my data while loading it in the script.
I am importing the data from a qvd and I have read on the internet that data can be sorted using order by only if it is resident in anothet table.
Therefore I am importing the qvd in a table called TEMP and then I am using this TEMP table as a Resident table for my data.
The TEMP is used as a resident table for my new table POC_TABLE (which I want to have the data sorted by declaration date) and POC_DAILY_SALES where I calculate some aggregations.
The issue is that when I drop the TEMP the only existing data that I have after running the below script is the data existing in POC_DAILY_SALES and nothin from POC_TABLE.
How can I obtain the POC_TABLE data ordered by date and also the information from POC_DAILY_SALES?
Thank you,
Oana
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Directory;
TEMP:
LOAD _KEY_Company,
_KEY_Family,
POC.DECLARATION_DATE as DECLARATION_DATE,
POC.DECLARATION_DATE-1 as DECLARATION_DATE_prev,
POC.ACTIVE_FOR_AVA as ACTIVE_FOR_AVA,
POC.FLAG_FOR_NDR as FLAG_FOR_NDR,
POC.POC_CODE as POC_CODE,
POC.ITEM_PART_NUMBER,
POC.ITEM_NAME,
POC.QTY_OUT,
POC.CURRENT_WEEK_OUT,
POC.PREVIOUS_WEEK_OUT,
POC.QTY_STOCK,
POC.CURRENT_WEEK_SOLD_OUT,
POC.PREVIOUS_WEEK_SOLD_OUT,
POC.UNSATISFIED_DEMAND,
POC.DEPLOYED,
POC.STK_IC_LESS_THAN_5,
POC.STK_IC_BTW_6_N_10,
POC.STK_IC_BTW_11_N_15,
POC.STK_IC_BTW_16_N_20,
POC.STK_IC_BTW_21_N_50,
POC.STK_IC_GREATER_THAN_50,
POC.STK_NEW_PROD,
POC.STK_NO_SALES,
POC.QTY_TRANSIT,
POC.CURRENT_MONTH_OUT,
POC.DAILY_AVG_CURRENT_WEEK_OUT
FROM
(qvd)
where POC.DECLARATION_DATE='31/01/2013' or POC.DECLARATION_DATE='24/01/2013' ;
POC_TABLE:
LOAD *
RESIDENT TEMP
order by DECLARATION_DATE;
POC_DAILY_SALES:
LOAD
POC_CODE,
DECLARATION_DATE,
ACTIVE_FOR_AVA,
_KEY_Family,
SUM(POC.QTY_OUT) AS Sum_Per_POC,
sum(POC.CURRENT_WEEK_OUT) as Sum_Per_POC_CurrentWeek,
sum(POC.PREVIOUS_WEEK_OUT) as Sum_Per_POC_PreviousWeek
RESIDENT TEMP
group by POC_CODE, DECLARATION_DATE,ACTIVE_FOR_AVA,_KEY_Family;
