Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
sarfaraz_sheikh
Contributor III
Contributor III

Different format code in Timestamp()

Dear All,

I have the below data

Input:

LOAD * Inline

[

InteractionID,     CreatedDate             ,  ResolvedDate

2065789,           03/20/201601:22:15 PM  ,  03/23/201609:15:07 AM

2065790,           03/15/2016 10:40:10 AM  ,  03/15/2016 10:15:04 PM

2065791,           03/18/2016 03:20:20 PM  ,  03/22/2016 11:10:10 AM

];


However, As you can see the first created date is in different format and rest of the dates is in different format code ...like below

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

'MM/DD/YYYYhh:mm:ss TT'


So my concern is how i can make one timestamp by combining diffrent format code


As of now i am writing like this and i am getting the result as expected ...but i want to combine them in One field.


Timestamp(Timestamp#(CreatedDate,'MM/DD/YYYY hh:mm:ss TT'),'MM/DD/YYYY hh:mm:ss') AS DateCreate,

Timestamp(Timestamp#(CreatedDate,'MM/DD/YYYYhh:mm:ss TT'),'MM/DD/YYYY hh:mm:ss') AS DateCreateeeee,

Kindly help how i can get around this.

hic‌ , gwassenaar‌  please comment

Sarfaraz






1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Use Alt():

Timestamp(Alt(

    Timestamp#(CreatedDate,'MM/DD/YYYY hh:mm:ss TT'),'MM/DD/YYYY hh:mm:ss',

    Timestamp#(CreatedDate,'MM/DD/YYYYhh:mm:ss TT'),'MM/DD/YYYY hh:mm:ss',

    )) AS DateCreate,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Use Alt():

Timestamp(Alt(

    Timestamp#(CreatedDate,'MM/DD/YYYY hh:mm:ss TT'),'MM/DD/YYYY hh:mm:ss',

    Timestamp#(CreatedDate,'MM/DD/YYYYhh:mm:ss TT'),'MM/DD/YYYY hh:mm:ss',

    )) AS DateCreate,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Kushal_Chawda

try like this

alt(timestamp(Timestamp#(DATE,'MM/DD/YYYYhh:mm:ss'),'MM/DD/YYYY hh:mm:ss'),

timestamp(Timestamp#(DATE,'MM/DD/YYYY hh:mm:ss'),'MM/DD/YYYY hh:mm:ss')) as Time

sarfaraz_sheikh
Contributor III
Contributor III
Author

Thanks Jonathan it is working fine as expected.....

However , One more query to ask you i want to calculate the network hours between CreatedDate and ResolvedDate ...

I am familier with NetworkDays() of Qlikview and it is giving me the result according to days but i want it on hourly basis..

Is this possible if yes then please help me

Sarfarax

jonathandienst
Partner - Champion III
Partner - Champion III

Please mark this thread as closed and open a new one with the new question. I will happily help you there if I can.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein