Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
davidvikings198
Contributor
Contributor

TimeStamp Field Question

I have 5 different tables that have a timestamp field call SysDate.  What i want to do is combined them together but have run into an issue:

1.  Four of the tables the timestamp field looks like 04/18/2018 10:30:32 AM

2. I have one the timestamp field looks like 04/18/2018 10:32


So it is missing the seconds and either the AM or PM.  My question is can I use the makedate function to solve my issue.  I would need to add either 00 to the seconds and then AM or PM.  The other option I was looking at is trimming off the seconds and AM/PM from the four table that include it.  I dont really want to do that.  Any suggestions would be great.


Thanks,

David

3 Replies
qlikmeplease
Creator III
Creator III

Well, its not an elegant solution , but please see below.

Timestamp(num(Date(MakeDate(Mid([YourDateTimeField],7,4),Left([YourDateTimeField],2),Mid([YourDateTimeField],4,2))))+Time(SubField([YourDateTimeField],' ',2),'hh:mm:ss TT'),'MM/DD/YYYY hh:mm:ss TT')

Change formats where needed.

Hope this helps.

maxgro
MVP
MVP

you can use the alt function (*)

Timestamp(Alt(

          Timestamp#(YourDateTimeField, 'MM/DD/YYYY hh:mm:ss tt'),

          Timestamp#(YourDateTimeField, 'MM/DD/YYYY hh:mm')

     ), 'MM/DD/YYYY hh:mm:ss')

(*)

QlikView Date fields

page 8

1.png

qlikmeplease
Creator III
Creator III

The above will also work, just a small change is needed in terms of the formatting to include the AM and PM.

Timestamp(Alt(


          Timestamp#(DateTime, 'MM/DD/YYYY hh:mm:ss tt'),


          Timestamp#(DateTime, 'MM/DD/YYYY hh:mm')


     ), 'MM/DD/YYYY hh:mm:ss TT')