Load file, Do Loop and/or Inter No ()

[#1]

I have a log file that containsthe following column sRECORD_ID, START_TIME and END_TIME ;

Record_ID	START_TIME	END_TIME
1234	08/02/2012 16:43:15	08/02/2012 17:02:08

As part of the load process, I need to create a line of data for EVERY MINUTE  that the report was actually open and create this as a DATETIMEKEY column.

I have tried to use the IterNo() function and DO Loop but not having much joy – This is what I am trying to achieve:

Record_ID	START_TIME	END_TIME	DATETIMEKEY
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:43
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:44
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:45
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:46
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:47
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:48
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:49
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:50
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:51
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:52
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:53
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:54
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:55
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:56
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:57
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:58
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 16:59
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 17:00
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 17:01
1234	08/02/2012 16:43:15	08/02/2012 17:02:08	08/02/2012 17:02

[stephencredmond · Accepted solution]

Hi,

You could auto-generate a calendar + time for every date / time combination that you could have and then inner join that to your data set using IntervalMatch.

Regards,

Stephen

[#3]

Thanks Stephen - I solved the issue using the IntervalMatch function