3 Replies Latest reply: Jul 31, 2013 7:42 AM by odiaconu RSS

    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

      [C:\#01 - Backup QVD\QVD\AVA\AVOPS_POCS_201301PLA005.QVD]

      (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
          Kaushik Solanki

          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

            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

            [C:\#01 - Backup QVD\QVD\AVA\AVOPS_POCS_201301PLA005.QVD]

            (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;