9 Replies Latest reply: Sep 18, 2012 7:59 AM by Luis Menendez RSS

    Where to place 'Order by' after using a Join Load

    Luis Menendez

      Good morning,

       

      I have this script which generates a table called 'BBDD' after adding the field 'FECHA' (date in english) in it.

       

      BBDD:
      LOAD H,
           CMP,
           M,
           MCDO,
           TO,
           TTOO,
           ID,
           CTO,
           FINI,
           FFIN,
           ACT,
           ES,
           INT,
           FIPE,
           FFPE,
           THA,
           Replace( BASE,'.',',') as BASE,
           IMP,
           FREC
      FROM
      C:\DATA_CON_20120917-115749.csv
      (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

       

      Join Load distinct
                FIPE,
                FFPE,
                Date(FIPE + iterno() -1) as FECHA
                resident BBDD
                While iterno() <= 1 + FFPE - FIPE;

       

      The table should result order by FECHA ASCENDANT but, because the field 'FECHA' is created in a second load (out of 'FROM', I suppose), I do not know where to place the command 'ORDER BY'.

       

      How can I get my table 'BBDD' sorted by 'FECHA' (date)?

       

      Thanks

        • Re: Where to place 'Order by' after using a Join Load
          David Ambrozie

          Hi,

           

          Try this:

           

          //Join

          temp:

          Load distinct

                    FIPE,

                    FFPE,

                    Date(FIPE + iterno() -1) as FECHA

                    resident BBDD

                    While iterno() <= 1 + FFPE - FIPE Resident BBDD;

           

          join (BBDD)

          load * resident temp order by FECHA asc;

           

          drop table BBDD;

           

          Regards,

          David

            • Re: Where to place 'Order by' after using a Join Load
              Luis Menendez

              Hi David, must be something wrong in the sintaxis from the word "Resident". The sintaxis debugger underlined the script from that point.

               

              Not recognized words after the sentence

               

              join

              temp:

              Load distinct

                        FIPE,

                        FFPE,

                        Date(FIPE + iterno() -1) as FECHA

                        resident BBDD

                        While iterno() <= 1 + FFPE - FIPE Resident BBDD

                • Re: Where to place 'Order by' after using a Join Load
                  whiteline _

                  While iterno() <= 1 + FFPE - FIPE Resident BBDD;

                    • Re: Where to place 'Order by' after using a Join Load
                      Luis Menendez

                      It gives me three Scripts errors:

                       

                      Tabla no encontrada (Table not found)
                      join (BBDD)

                      load * resident temp order by FECHA asc

                       

                      Línea de error de Script: (error line of script)

                      DateField:

                      load Date( + rowno() -1 ) as TempDate

                      AutoGenerate

                      -+1

                       

                      Tabla no encontrada (Table not found)

                      DROP TABLES statement

                        • Re: Where to place 'Order by' after using a Join Load
                          whiteline _

                          Post all your script.

                            • Re: Where to place 'Order by' after using a Join Load
                              Luis Menendez

                              ///$tab Main
                              SET ThousandSep='.';
                              SET DecimalSep=',';
                              SET MoneyThousandSep='.';
                              SET MoneyDecimalSep=',';
                              SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
                              SET TimeFormat='h:mm:ss';
                              SET DateFormat='DD/MM/YYYY';
                              SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';
                              SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
                              SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

                              ///$tab Explosion
                              BBDD:
                              LOAD H,
                                   CMP,
                                   M,
                                   MCDO,
                                   TO,
                                   TTOO,
                                   ID,
                                   CTO,
                                   FINI,
                                   FFIN,
                                   ACT,
                                   ES,
                                   INT,
                                   FIPE,
                                   FFPE,
                                   THA,
                                   Replace( BASE,'.',',') as BASE,
                                   IMP,
                                   FREC
                              FROM
                              C:\DATA_CON_20120917-115749.csv
                              (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                              join Load distinct
                                        FIPE,
                                        FFPE,
                                        Date(FIPE + iterno() -1) as FECHA
                                        resident BBDD
                                        While iterno() <= 1 + FFPE - FIPE;
                              ///$tab Calendario
                              let varmindate = num(Peek('FECHA',0,'BBDD'));
                              let varmaxdate = num(Peek('FECHA',-1,'BBDD'));
                              let vartoday = num(Today());

                              DateField:
                              load Date($(varmindate) + rowno() -1 ) as TempDate
                              AutoGenerate
                              $(varmaxdate)-$(varmindate)+1;

                              MasterCalendar:
                              LOAD
                              TempDate as FECHA,
                              Day(TempDate) as c_day,
                              WeekDay(TempDate) as c_weekday,
                              Week(TempDate) as c_week,
                              Month(TempDate) as c_month,
                              Year(TempDate) as c_year,
                              Date (monthstart (TempDate) , 'MMM_YYYY') as c_monthyear,
                              Week(TempDate)&'-'&Year(TempDate) AS c_weekyear,
                              inyeartodate(TempDate, $(vartoday), 0) * -1 AS c_curytdflag,
                              inyeartodate(TempDate, $(vartoday), -1) * -1 AS c_lastytdflag
                              RESIDENT DateField
                              ORDER BY TempDate ASC;

                              DROP TABLE DateField;

                                • Re: Where to place 'Order by' after using a Join Load
                                  whiteline _

                                  There is no code:

                                  "join (BBDD)

                                  load * resident temp order by FECHA asc"

                                   

                                  Where is the error occurs ?

                                  • Re: Where to place 'Order by' after using a Join Load
                                    whiteline _

                                    Just add bolded text in a proper place:

                                     

                                    ///$tab Main

                                    SET ThousandSep='.';

                                    SET DecimalSep=',';

                                    SET MoneyThousandSep='.';

                                    SET MoneyDecimalSep=',';

                                    SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

                                    SET TimeFormat='h:mm:ss';

                                    SET DateFormat='DD/MM/YYYY';

                                    SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

                                    SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

                                    SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

                                     

                                    ///$tab Explosion

                                    BBDD:

                                    LOAD H,

                                         CMP,

                                         M,

                                         MCDO,

                                         TO,

                                         TTOO,

                                         ID,

                                         CTO,

                                         FINI,

                                         FFIN,

                                         ACT,

                                         ES,

                                         INT,

                                         FIPE,

                                         FFPE,

                                         THA,

                                         Replace( BASE,'.',',') as BASE,

                                         IMP,

                                         FREC

                                    FROM

                                    C:\DATA_CON_20120917-115749.csv

                                    (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                                     

                                    join Load distinct

                                              FIPE,

                                              FFPE,

                                              Date(FIPE + iterno() -1) as FECHA

                                              resident BBDD

                                              While iterno() <= 1 + FFPE - FIPE;

                                     

                                    BBDDNew:

                                    NoConcatenate

                                    LOAD

                                             *

                                    RESIDENT  BBDD

                                    order by FECHA asc;

                                     

                                    drop table BBDD;

                                    rename table BBDDNew to BBDD;

                                     

                                     

                                    ///$tab Calendario

                                    let varmindate = num(Peek('FECHA',0,'BBDD'));

                                    let varmaxdate = num(Peek('FECHA',-1,'BBDD'));

                                    let vartoday = num(Today());

                                     

                                    DateField:

                                    load Date($(varmindate) + rowno() -1 ) as TempDate

                                    AutoGenerate

                                    $(varmaxdate)-$(varmindate)+1;

                                     

                                    MasterCalendar:

                                    LOAD

                                    TempDate as FECHA,

                                    Day(TempDate) as c_day,

                                    WeekDay(TempDate) as c_weekday,

                                    Week(TempDate) as c_week,

                                    Month(TempDate) as c_month,

                                    Year(TempDate) as c_year,

                                    Date (monthstart (TempDate) , 'MMM_YYYY') as c_monthyear,

                                    Week(TempDate)&'-'&Year(TempDate) AS c_weekyear,

                                    inyeartodate(TempDate, $(vartoday), 0) * -1 AS c_curytdflag,

                                    inyeartodate(TempDate, $(vartoday), -1) * -1 AS c_lastytdflag

                                    RESIDENT DateField

                                    ORDER BY TempDate ASC;

                                     

                                    DROP TABLE DateField;