7 Replies Latest reply: Jul 3, 2017 3:24 PM by Macarena Gonzalez RSS

    Date format (in spanish)

    macarena gonzalez

      Hi! I have a column (named "Fecha") with the following format: 1505/2017 01:50:30 pm. What I would like to do is extract from that data the fields: año(Year in english), mes(month in english), dia (day in english), hora (hour in english). The script right now looks like this:

       

      Directorio;

      Hora CARGA,

           Fecha,

           [Sucursal Origen],

           Concepto,

           Referencia,

           Importe como importe,

           Banco,

           Cuenta,

           rubro,

           sub_rubro,

       

       

      DE

      [UltimosMovimientossantander 01-5 al 01-6.xls]

      (Biff, etiquetas incrustadas, tabla es [Ultimos Movimientos $]);


      * Important: I am working in SPANISH, and I would like the name of days and month to me in spanish as well.


      Thank you in advance!

        • Re: Date format (in spanish)
          Sunny Talwar

          May be this

           

          Directorio;

          LOAD *,

               Year(Fecha) as año,

               Month(Fecha) as Mes,

               Day(Fecha) as Dia;

          Hora CARGA,

               Date#(Fecha, 'DD/MM/YYYY hh:mm:ss TT') as Fecha

               [Sucursal Origen],

               Concepto,

               Referencia,

               Importe como importe,

               Banco,

               Cuenta,

               rubro,

               sub_rubro,

          DE

          [UltimosMovimientossantander 01-5 al 01-6.xls]

          (Biff, etiquetas incrustadas, tabla es [Ultimos Movimientos $]);

            • Re: Date format (in spanish)
              macarena gonzalez

              Gracias!

              It did not work though. I tried several forms. My last shot was this. Could you please tell me what is wrong with this? Thank you

               

              LOAD

                   Year(date) as año,

                   Month(date) as Mes,

                   Day(date) as Dia;

               

              LOAD

                   Date#(Fecha, 'DD/MM/YYYY hh:mm:ss TT') as Fecha,

                   [Sucursal Origen],

                   Concepto,

                   Referencia,

                   Importe as importe,

                   Banco,

                   Cuenta,

                rubro,

                   sub_rubro

               

              FROM

               

               

              [UltimosMovimientossantander 01-5 al 01-6.xls]

               

               

              (Biff, etiquetas incrustadas, tabla es [Ultimos Movimientos $]);

                • Re: Date format (in spanish)
                  Sunny Talwar

                  Difficult to say, but may be your date format is not this

                  DD/MM/YYYY hh:mm:ss TT


                  Can you run this and share a screenshot of a filter box for Fecha

                  LOAD

                       Fecha

                  FROM

                  [UltimosMovimientossantander 01-5 al 01-6.xls]

                  (Biff, etiquetas incrustadas, tabla es [Ultimos Movimientos $]);

                    • Re: Date format (in spanish)
                      macarena gonzalez

                      I checked, the date did not have the hour in it. I tried this instead and it did not work either. I attach

                      the 'fecha' table.

                       

                      LOAD

                           Year(date) as año,

                           Month(date) as Mes,

                           Day(date) as Dia;

                       

                      LOAD

                           Date#(Fecha, 'DD/MM/YYYY hh:mm:ss TT') as Fecha,

                           [Sucursal Origen],

                           Concepto,

                           Referencia,

                           Importe as importe,

                           Banco,

                           Cuenta,

                        rubro,

                           sub_rubro

                       

                      FROM

                       

                       

                      [UltimosMovimientossantander 01-5 al 01-6.xls]

                       

                       

                      (Biff, etiquetas incrustadas, tabla es [Ultimos Movimientos $]);screen.JPG

                        • Re: Date format (in spanish)
                          Sunny Talwar

                          Try this

                           

                          Directorio;

                          LOAD *,

                               Year(Fecha) as año,

                               Month(Fecha) as Mes,

                               Day(Fecha) as Dia;

                          Hora CARGA,

                               Date#(Fecha, 'DD/MM/YYYY') as Fecha

                               [Sucursal Origen],

                               Concepto,

                               Referencia,

                               Importe como importe,

                               Banco,

                               Cuenta,

                               rubro,

                               sub_rubro,

                          DE

                          [UltimosMovimientossantander 01-5 al 01-6.xls]

                          (Biff, etiquetas incrustadas, tabla es [Ultimos Movimientos $]);

                  • Re: Date format (in spanish)
                    Peter Cammaert

                    And on top of Sunny's feedback, if you want to have day name and monthnames in Spanish, check the SET statements at the beginning of every script. If your Windows copy is a Spanish language version, the month names should already be in Spanish.

                     

                    If not, change the value lists of the following variables to carry the correct names and reload.

                     

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

                    SET DayNames='lu;ma;mi;ju;vi;sa;do';

                     

                    Change them at will if for example you live in a different region.

                    • Re: Date format (in spanish)
                      Macarena Gonzalez

                      Thank you!

                       

                      And how can I do the same thing if the date format is ´Monday, jun 29´? I have all the dates in that format. Qlikview recognizes it, but I cant extract the date and month number.