topic Re: Two different time formats in one column. in QlikView

Two different time formats in one column.

qlikconsultant — Mon, 31 Aug 2015 12:00:05 GMT

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?

Re: Two different time formats in one column.

shair_abbas — Mon, 31 Aug 2015 12:05:31 GMT

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

Re: Two different time formats in one column.

amit_saini — Mon, 31 Aug 2015 12:06:56 GMT

Hi Markus,

Try this:

or follow:

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

Thanks,
AS

Re: Two different time formats in one column.

maxgro — Mon, 31 Aug 2015 12:08:39 GMT

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

];

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.

PrashantSangle — Mon, 31 Aug 2015 12:08:44 GMT

Hi,

use ALT()

check in Help menu.

Regards

Re: Two different time formats in one column.

qlikmsg4u — Mon, 31 Aug 2015 12:13:57 GMT

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

];

Re: Two different time formats in one column.

sasiparupudi1 — Mon, 31 Aug 2015 12:14:25 GMT

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

[

24.03.2014 12:57

2014-03-24 14:42:05.093

];

Re: Two different time formats in one column.

Kushal_Chawda — Mon, 31 Aug 2015 12:16:58 GMT

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