Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Not applicable

Date format (in spanish)

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!

7 Replies

Re: Date format (in spanish)

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 $]);

Not applicable

Re: Date format (in spanish)

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)

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 $]);

Not applicable

Re: Date format (in spanish)

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)

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)

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.

gonzalezmacaren
New Contributor

Re: Date format (in spanish)

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.