Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sorting a table when loading data

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;

1 Solution

Accepted Solutions
Kushal_Chawda

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;

View solution in original post

3 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Kushal_Chawda

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;

Not applicable
Author

thanks for the above

Oana