Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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 $]);

Not applicable
Author

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

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

Not applicable
Author

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

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

Anonymous
Not applicable
Author

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.