topic Re: Convert Time Stamp in QlikView

Convert Time Stamp

Thu, 03 Sep 2015 11:38:31 GMT

Hello i have mixed Time Stamps on these fields, i want all the date fields to be like the first 5 date fields,

How can i convert this in Qlik Sense ?

2001-12-06 23:00

2001-12-06 23:12

2001-12-06 23:22

2001-12-06 23:38

2001-12-06 23:39

1/13/06 0:00

1/13/06 0:01

1/13/06 0:09

1/13/06 0:28

1/13/06 1:12

1/13/06 1:30

Re: Convert Time Stamp

PrashantSangle — Thu, 03 Sep 2015 11:40:58 GMT

Hi,

Use ALt()

see in help menu.

Regards

Re: Convert Time Stamp

hic — Thu, 03 Sep 2015 11:45:42 GMT

TimeStamp(

Alt(

TimeStamp#(Field,'YYYY-MM-DD hh:mm'),

TimeStamp#(Field,'M/D/YY h:mm')

'YYYY-MM-DD hh:mm'

)

HIC

Re: Convert Time Stamp

Thu, 03 Sep 2015 12:47:59 GMT

Hello Henric, thanks for the reply,

I am glad to have a new insight in date formats, but i found the real problem :

It was on the source excel file, i wanted to change the comma delimiters to tabs in Excel, and after load it to Sense.

That was the problem, it automatically formatted the date fields very wrong when i tried to change delimiters in Excel.

Now i am trying to get WeekNumbers, DayName and Hours, to analyze more,

But i get nothing, what am i doing wrong ?

This is what i used :

[SacramentocrimeJanuary2006]:

LOAD

//[cdatetime],

[address],

[district],

[beat],

[grid],

[crimedescr],

[ucr_ncic_code],

[latitude],

[longitude],

GeoMakePoint(num([latitude]),num([longitude])) as GeoKey,

(TimeStamp (

Alt(

TimeStamp#([cdatetime],'YYYY-MM-DD hh:mm'),

TimeStamp#([cdatetime],'M/D/YY h:mm')

'YYYY-MM-DD hh:mm'

) ) as NewCD ,

DayName('NewCD'),

Hour('NewCD'),

Week('NewCD')

FROM [lib://Downloads/SacramentocrimeJanuary2006.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Convert Time Stamp

qlikviewwizard — Thu, 03 Sep 2015 12:49:52 GMT

Hi,

Please use the full script as advised by HIC.

Data:

LOAD *,TimeStamp(

Alt(

TimeStamp#(Date_Value,'YYYY-MM-DD hh:mm'),

TimeStamp#(Date_Value,'M/D/YY h:mm')

'YYYY-MM-DD hh:mm'

) as New_Date_Value;

Load * inline [

Date_Value

2001-12-06 23:00

2001-12-06 23:12

2001-12-06 23:22

2001-12-06 23:38

2001-12-06 23:39

1/13/06 0:00

1/13/06 0:01

1/13/06 0:09

1/13/06 0:28

1/13/06 1:12

1/13/06 1:30

];

Re: Convert Time Stamp

qlikviewwizard — Thu, 03 Sep 2015 12:51:47 GMT

Hi,

Can you attach the excel file to demonstrate your issue?

Thank you.

Re: Convert Time Stamp

Thu, 03 Sep 2015 12:56:43 GMT

Hi, i already did use it and formatted the datefields.

My Question was to get Hours, WeekNumbers and DayName.

Re: Convert Time Stamp

sasiparupudi1 — Thu, 03 Sep 2015 12:57:48 GMT

You can not use a calculated field in the script...use a preceding load instead

load *,

DayName('NewCD'),

Hour('NewCD'),

Week('NewCD')

;

LOAD

//[cdatetime],

[address],

[district],

[beat],

[grid],

[crimedescr],

[ucr_ncic_code],

[latitude],

[longitude],

GeoMakePoint(num([latitude]),num([longitude])) as GeoKey,

(TimeStamp (

Alt(

TimeStamp#([cdatetime],'YYYY-MM-DD hh:mm'),

TimeStamp#([cdatetime],'M/D/YY h:mm')

'YYYY-MM-DD hh:mm'

) ) as NewCD ,

FROM [lib://Downloads/SacramentocrimeJanuary2006.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);