topic Interval match within multiple instances in App Development

Interval match within multiple instances

lionking15 — Thu, 07 Apr 2022 08:31:52 GMT

Hi Team,

I am struggling to crack below given logic, Where we need to get the interval match with multiple time instances

Need to create bucket(i.e. interval) on the basis of start and end

Input Table:

Job Id	Start	End
1	08:15am	11:13am

Output Table:

Job Id	Start	End	Interval
1	08:15am	11:13am	8am
1	08:15am	11:13am	9am
1	08:15am	11:13am	10am
1	08:15am	11:13am	11am

Re: Interval match within multiple instances

Qbo — Thu, 07 Apr 2022 08:57:34 GMT

I'd try doing it the other way around, should get you the result you want if you create the interval hours and match them in.

For example:

IntervalHours: LOAD time(Hours,'hh:mm') as Hours Inline[ Hours 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00 ]; OrderLog: LOAD time(Start,'hh:mm') as Start, time(End,'hh:mm') as End, Job_Id as [Job Id] INLINE [ Start, End, Job_Id 08:15, 11:13, 1 ]; Inner Join IntervalMatch (Hours) LOAD Start, End Resident OrderLog;

Re: Interval match within multiple instances

justISO — Thu, 07 Apr 2022 09:24:16 GMT

Hi, I have other solution, but not sure how it will work with am, pm format. Maybe you can reuse it to your solution:

temp: load * inline [ JobId, Start, End, 1, 08:15 , 11:13 2 , 09:00, 14:00 ]; NoConcatenate main_temp: load * resident temp; concatenate(main_temp) load distinct JobId, Start, End, hour(Start)+iterno()-1 as Interval resident temp while hour(Start) + iterno()-1 <= hour(End); NoConcatenate main: load * resident main_temp where not IsNull(Interval); drop table temp,main_temp;

Re: Interval match within multiple instances

lionking15 — Thu, 07 Apr 2022 12:08:30 GMT

Thanks @justISO It worked for me😀