Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 $]);
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 $]);
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 $]);
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 $]);
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 $]);
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.
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.