Convert Time Stamp

[#1]

  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

[PrashantSangle]

Hi,

Use ALt()

see in help menu.

Regards

[hic · Accepted solution]

TimeStamp(

   Alt(

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

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

      ),

      'YYYY-MM-DD hh:mm'

   )

HIC

[#4]

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]

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

];

[qlikviewwizard]

Hi,

Can you attach the excel file to demonstrate your issue?

Thank you.

[#7]

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

My Question was to get Hours, WeekNumbers and DayName.

[sasiparupudi1]

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