Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to make a timestamp out of two fields?

Dear all,

I've been trying to solve this time issue for days now and hope someone could help me out.

What I want to know is the processing time for an activity to be performed. I got date fields with the format 'YYYY-MM-DD' and time fields with the format 'NNNN'. e.g '1' is 00:01 AM, and '213' the same as 2:13 AM. What I want is to have both the date and the time fields combinded so I get the format 'YYYY-MM-DD hh:mm' so I can count how long time in hours the processing time is.

I thought I could use the timestamp()-function but maybe I'm missing out on something or using the wrong function to solve this.



ActivityProcess:
Load *,
//Makes a time format out of the time field
if(len(Start_time)<3,'00:'&time(time#(Start_time,'mm'),'mm'),
if(len(Start_time)<4 and len(Start_time)>2, time(time#(left(Start_time,1),'hh'), 'hh')&':'&time(time#(right(v,2),'mm'),'mm'),
if(len(Start_time)<5 and len(Start_time)>2, time(time#(left(Start_time,2),'hh'), 'hh')&':'&time(time#(right(Start_time,2),'mm'),'mm')))) as Start_time,
if(len(End_time)<3,'00:'&time(time#(End_time,'mm'),'mm'),
if(len(End_time)<4 and len(End_time)>2, time(time#(left(End_time,1),'hh'), 'hh')&':'&time(time#(right(v,2),'mm'),'mm'),
if(len(End_time)<5 and len(End_time)>2, time(time#(left(End_time,2),'hh'), 'hh')&':'&time(time#(right(End_time,2),'mm'),'mm')))) as End_time,
timestamp(timestamp#(End_date&End_time, 'YYYY-MM-DD hh:mm')-timestamp#(Start_date&Start_time, 'YYYY-MM-DD hh:mm'), 'YYYY-MM-DD hh:mm') as ProcessTime;
Load
Activity_ID,
Start_date,
Start_time,
End_date,
End_time
From ..

If I don't get the date and the time in the format 'YYYY-MM-DD hh:mm' I don't know how to do the interval count in hours.

Kind regards
Mi

Community Browser