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 and time fields- How to create derived fields

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;

1 Solution

Accepted Solutions
sunny_talwar

For the MonthYear field that you are creating, you can create it using one of the two approaches:

LOAD *,

  Year(Fecha) as Year,

    Month(Fecha) as Month,

    Time(Frac(Fecha)) as Time,

    Date(Floor(Fecha)) as Date,

    MonthName(Fecha) as MonthYear,

    Date(Fecha, 'MM-YYYY') as MonthYear1;

LOAD

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

  Producto,

    "Categoría 1",

    "Categoría 2",

    "Categoría 3",

    Cantidad,

    "Ventas Netas",

    EN_CTA_CTE,

    Sucursal,

    Vendedor,

    "Costo Total",

    Proveedor,

    Cliente,

    "Condcion Comercial",

    Mes,

    Año,

    Día,

    Ciudad,

    Segmento

FROM [lib://prueba/Datos Reales.xlsx](ooxml, embedded labels, table is [Base de datos]);

I personally like MonthName approach, but I have seen the other being use as well.

View solution in original post

12 Replies
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;

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

>>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?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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

Not applicable
Author

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')

Not applicable
Author

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