Time with and without colon format issue

[Anonymous]

Due to some changes, my application is producing screwed data because of the time format. The sql table that I am using stores time with and without colon. How can I make it so that all the records have a colon so that I can do time difference calculation?

This is how it looks:

Time

01:20 - (this is right)

23:40 - (this is right)

1150 - (this is wrong - Not getting colon)

22:10 - (this is right)

How can I make the column to have specific format with colon in between?

Thanks you.

[sunny_talwar · Accepted solution]

Try this:

Time(Alt(Time#(Time, 'hh:mm'), Time#(Time, 'hhmm')), 'hh:mm') as Time

[Anonymous]

I forgot to mention that some records does not have time response at all. Will your solution work in that case too with blank times? I will give your code a try after my meeting and will update you. Thanks Sunny.

[sunny_talwar]

I believe it should still work.

[Anonymous]

Looks like this solution worked. Let me confirm with few more records then we can mark it as an answer.

You are great. Thanks.

[sunny_talwar]

No rush, take your time

[Anonymous]

Sunny,

Your solution did the trick. It worked.

Time(Alt(Time#(Time, 'hh:mm'), Time#(Time, 'hhmm')), 'hh:mm') as Time

I don't know why but I don't get an option to mark your code as an answer. Please go ahead and mark it as an answer on my behalf. Thank you so much.

[sunny_talwar]

Hahaha I don't have the option to mark my own answer as correct. Its the person who posted the question (which is you in this case) can mark the correct answer

[Anonymous]

I was able to mark it as helpful. Will check back later to see if "Mark as answer" option opens up.

Thanks again.

[MarcoWedel]

Hi,

maybe helpful:

Qlik Community Tip: Marking Replies as Correct or Helpful

regards

Marco