Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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
Kush
MVP
MVP

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

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Kush
MVP
MVP

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

Not applicable
Author

thanks for the above

Oana