Different format code in Timestamp()

[sarfaraz_sheikh]

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

[jonathandienst · Accepted solution]

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,

[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]

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]

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