topic Re: Make Date Time from two fields? in QlikView

Make Date Time from two fields?

mikecrengland — Fri, 07 Oct 2016 20:19:37 GMT

Hello All -

I'm trying to combine a date field and a time field into a timestamp field.

The original data comes from an AS400 and is formatted as integers. My attempt failed... My script looks like:

LOAD

MoveNumber

,Timestamp(ScheduledEarliest & ScheduledEarliestTime) AS ScheduledEarliestDateTime

,ScheduledEarliest

,ScheduledEarliestTime

;

LOAD

Text(MoveNumber) AS MoveNumber

,MakeDate(Left(ScheduledEarliest,4),Right(Left(ScheduledEarliest,6),2),Right(ScheduledEarliest,2)) AS ScheduledEarliest

,MakeTime(Left(Right('0000' & ScheduledEarliestTime,4),2),Right(Right('0000' & ScheduledEarliestTime,4),2)) AS ScheduledEarliestTime

;

LOAD

Text(MOVENUMBER) AS MoveNumber

,SCHEDULEDEARLIEST AS ScheduledEarliest

,SCHEDULEDEARLIESTTIME AS ScheduledEarliestTime

;

Any thoughts on where I messed up?

Thanks,

mike

Re: Make Date Time from two fields?

sunny_talwar — Fri, 07 Oct 2016 20:22:19 GMT

How does

SCHEDULEDEARLIEST and

SCHEDULEDEARLIESTTIME

look in there rawest form?

Re: Make Date Time from two fields?

sunny_talwar — Fri, 07 Oct 2016 20:23:23 GMT

Also, I would do something like this:

Intstead of &

Timestamp(ScheduledEarliest & ScheduledEarliestTime) AS ScheduledEarliestDateTime

I would use +

Timestamp(ScheduledEarliest + ScheduledEarliestTime) AS ScheduledEarliestDateTime

Re: Make Date Time from two fields?

mikecrengland — Fri, 07 Oct 2016 20:23:49 GMT

They are.

SCHEDULEDEARLIEST might look like 20160214

and

SCHEDULEDEARLIESTTIME might look like 734 (7:34 AM

Re: Make Date Time from two fields?

mikecrengland — Fri, 07 Oct 2016 20:25:00 GMT

That did it!!

Thank you!!

Re: Make Date Time from two fields?

sunny_talwar — Fri, 07 Oct 2016 20:25:25 GMT

Sweet