topic Date format (in spanish) in QlikView

Date format (in spanish)

Mon, 05 Jun 2017 17:09:48 GMT

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,

[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 — Mon, 05 Jun 2017 17:56:37 GMT

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,

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

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

Re: Date format (in spanish)

Tue, 06 Jun 2017 13:26:35 GMT

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 — Tue, 06 Jun 2017 14:03:32 GMT

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)

Tue, 06 Jun 2017 15:05:57 GMT

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

Re: Date format (in spanish)

sunny_talwar — Sun, 11 Jun 2017 07:07:34 GMT

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,

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

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

Re: Date format (in spanish)

Peter_Cammaert — Sun, 11 Jun 2017 18:42:06 GMT

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)

Anonymous — Mon, 03 Jul 2017 19:24:41 GMT

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.