Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

qlikconsultant
Contributor II

Two different time formats in one column.

I have a csv-file with 2 different time formats in one column.

24.03.2014 12:57 and

2014-03-24 14:42:05.093

How can I format it in one Format?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Two different time formats in one column.

load

  field,

  date(alt(Date#(field,'DD.MM.YYYY hh:mm'), Date#(field,'YYYY-MM-DD hh:mm:ss.fff'))) as newfield

inline [

field

24.03.2014 12:57

2014-03-24 14:42:05.093

];

1.png

the same expression works in a chart

alt(Date#(field,'DD.MM.YYYY hh:mm'), Date#(field,'YYYY-MM-DD hh:mm:ss.fff'))

7 Replies
Partner
Partner

Re: Two different time formats in one column.

In Number tab of properties you can set the data format whatever want...

amit_saini
Honored Contributor III

Re: Two different time formats in one column.

Hi Markus,

Try this:

or follow:

https://community.qlik.com/message/804032#804032

Thanks,
AS

MVP
MVP

Re: Two different time formats in one column.

load

  field,

  date(alt(Date#(field,'DD.MM.YYYY hh:mm'), Date#(field,'YYYY-MM-DD hh:mm:ss.fff'))) as newfield

inline [

field

24.03.2014 12:57

2014-03-24 14:42:05.093

];

1.png

the same expression works in a chart

alt(Date#(field,'DD.MM.YYYY hh:mm'), Date#(field,'YYYY-MM-DD hh:mm:ss.fff'))

Re: Two different time formats in one column.

Hi,

use ALT()

check in Help menu.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
qlikmsg4u
Valued Contributor

Re: Two different time formats in one column.

If i understand correctly you want to do it while loading? if yes

try this

load

*,

  Timestamp(alt(Date#(Date,'DD.MM.YYYY hh:mm'), Date#(Date,'YYYY-MM-DD hh:mm:ss.fff'))) as DateNew

inline [

Date

24.03.2014 12:57

2014-03-24 14:42:05.093

];

sasiparupudi1
Honored Contributor III

Re: Two different time formats in one column.

LOAD dt,Timestamp(alt(Timestamp#(trim(dt),'DD.MM.YYYY hh:mm'),Timestamp#(trim(dt),'YYYY-MM-DD hh:mm:ss[.fff]'))) as newDT Inline

[

dt

24.03.2014 12:57

2014-03-24 14:42:05.093

];

Re: Two different time formats in one column.

create a another field as below

alt(timestamp(timestamp#(Date,'DD.MM.YYYY hh:mm'),'DD/MM/YYYY hh:mm:ss'),

timestamp(timestamp#(Date,'YYYY-MM-DD h:mm:ss[.fff]'),'DD/MM/YYYY hh:mm:ss')) as Date