Date and time fields- How to create derived fields

[#1]

Hello Everyone! I need your help for configuring date and time fields.

I have the date in the following format: 201503111647000 ( 2015 03 11 16:47:000). The field is called "Fecha" in the database and it is in text format.

How can I create the derived fields from the field "Fecha"?

I need the Year (2015), Month (11) ,Day (3), Hour (16:47), Month names( Jan, Feb,..), day names (Monday, Tuesday..), year month names( Jan 15, Feb 15)

I would really appreciate your help .Thank you!!!

LOAD

    Cliente,

    Sucursal,

    Vendedor,

    "Unidad de Medida",

    "Condcion_Comercial",

    Cantidad,

    "Costo Total",

    "Ventas Netas",

     Fecha,

SQL SELECT

    Cliente,

    Sucursal,

    Vendedor,

    "Unidad de Medida",

    "Condcion_Comercial" as "Condición Comercial",

    Cantidad,

    "Costo Total",

    "Ventas Netas",

    "Contenido total Vendido",

    Transaccion,

    "Tipo de comprobante",

    Fecha

FROM "C:\USERS\IGNACIO\Documents\Proyecto Pintecord\Base de datos Ventas.accdb".Ventas;

[sunny_talwar]

Try this:

LOAD *,

          Year(Fecha) as Year,

          Month(Fecha) as Month,

          Time(Frac(Fecha)) as Time,

          Date(Floor(Fecha)) as Date;

LOAD TimeStamp(TimeStamp#(Fecha, 'YYYYMMDDhhmmsss'), 'YYYY MM DD hh:mm:ss') as Fecha,

          allotherfields

FROM source;

[#3]

Hello Sunny Talwar, thank you for your help. It partially works, but the time is not good.

According to the register it should be 14:26 pm, but it indicates 09:26:40 pm.

Do you or anyone knows how to create the derived fields?

This way the new fields could appears in the date & time dimensions

Thanks

[sunny_talwar]

Can you share the input number for that particular record? Its just weird it did that.

[jonathandienst]

>>According to the register it should be 14:26 pm, but it indicates 09:26:40 pm.

That looks like a time zone difference. The QV timestamp has no timezone indication, so the numerical value contained the time 09:26 (fractional value 0.393056)

What do you mean by "register"? Perhaps your source system corrects the time according to the time zone in the register, but stores it in GMT -- and this is the value retrieved by QV?

[Anonymous]

Can we do something like this:

Test:

LOAD

    Cliente,

    Sucursal,

    Vendedor,

    "Unidad de Medida",

    "Condcion_Comercial",

    Cantidad,

    "Costo Total",

    "Ventas Netas",

     Fecha,

SQL SELECT

    Cliente,

    Sucursal,

    Vendedor,

    "Unidad de Medida",

    "Condcion_Comercial" as "Condición Comercial",

    Cantidad,

    "Costo Total",

    "Ventas Netas",

    "Contenido total Vendido",

    Transaccion,

    "Tipo de comprobante",

    Fecha

FROM "C:\USERS\IGNACIO\Documents\Proyecto Pintecord\Base de datos Ventas.accdb".Ventas;

////Resident Load

load

Fecha,

mid(Fecha,1,4) as Year,

mid(Fecha,5,6) as Day,

mid(Fecha,7,8) as Period

and

so on....

resident Test;

I think it should work if it is same format for Fecha.

[Kushal_Chawda]

try

timestamp(makedate(Mid(Fecha,1,4),mid(Fecha,5,2),mid(Fecha,7,2)) + maketime(Mid(Fecha,9,2),mid(Fecha,11,2),mid(Fecha,13,3)),'DD/MM/YYYY hh:mm:ss') as Fecha

or

timestamp(timestamp#(Fecha,'YYYYMMDDhhmmss.[fff]'),'DD/MM/YYYY hh:mm:ss') as Fecha

[#8]

The input number is "20151108143025000" ,which means 11/08/2015 14:30:25  or 02:30:25 P.M

Note that the number is in text format in the database

Thanks

[sunny_talwar]

This should fix it:

TimeStamp(TimeStamp#(Fecha, 'YYYYMMDDhhmmssfff'), 'YYYY MM DD hh:mm:ss TT')

[#10]

Very interesting fact, Is it possible to configure the timezone?

I meant that the database has the record "20151108143025000" that should be 14:30:25 hs, but in qliksense it appears as 09:26 PM

Thanks