topic Re: Sorting a table when loading data in QlikView

Sorting a table when loading data

Wed, 31 Jul 2013 09:16:00 GMT

Hi,

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;

drop table TEMP;

Re: Sorting a table when loading data

kaushiknsolanki — Wed, 31 Jul 2013 09:19:46 GMT

Hi,

Try this.

POC_TABLE:

LOAD *,'1' as Dummy;

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;

drop table TEMP;

Drop field Dummy from POC_TABLE;

Regards,

Kaushik Solanki

Re: Sorting a table when loading data

Kushal_Chawda — Wed, 31 Jul 2013 09:22:49 GMT

Hi,

You are missing NOCONCATENATE keyword while taking resident.

Use the below scrip

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:

NOCONCATENATE

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;

drop table TEMP;

Re: Sorting a table when loading data

Wed, 31 Jul 2013 11:42:22 GMT

thanks for the above

Oana