Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Convert Time Stamp

  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:00
1/13/06 0:01
1/13/06 0:01
1/13/06 0:01
1/13/06 0:01
1/13/06 0:09
1/13/06 0:28
1/13/06 1:12
1/13/06 1:12
1/13/06 1:30
1 Solution

Accepted Solutions
Henric_Cronström

TimeStamp(

   Alt(

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

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

      ),

      'YYYY-MM-DD hh:mm'

   )

HIC

View solution in original post

7 Replies
PrashantSangle

Hi,

Use ALt()

see in help menu.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Henric_Cronström

TimeStamp(

   Alt(

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

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

      ),

      'YYYY-MM-DD hh:mm'

   )

HIC

Not applicable
Author

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

qlikviewwizard
Master II
Master II

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:00

1/13/06 0:01

1/13/06 0:01

1/13/06 0:01

1/13/06 0:01

1/13/06 0:09

1/13/06 0:28

1/13/06 1:12

1/13/06 1:12

1/13/06 1:30

];

Capture.PNG

qlikviewwizard
Master II
Master II

Hi,

Can you attach the excel file to demonstrate your issue?

Thank you.

Not applicable
Author

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

My Question was to get Hours, WeekNumbers and DayName.

sasiparupudi1
Master III
Master III

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