Group by Interval Time

[inigoelv]

Good morning,

I have a question concerning to Interval time.

I have a table of consume like this:

Supplier	Fecha
A	01/01/2020 03:45
A	01/01/2020 05:30
B	01/01/2020 06:56
A	01/01/2020 09:00
A	01/01/2020 10:34
B	01/01/2020 11:23

What I pretend is a table like this:

Supplier	From	To
A	01/01/2020 03:45	01/01/2020 06:56
B	01/01/2020 06:56	01/01/2020 09:00
A	01/01/2020 09:00	01/01/2020 11:23
B	01/01/2020 11:23	.......

I imagine the solution will be with intervalmatch, but I can not solve this grouping.

Thanks in advance.

[Taoufiq_Zarra]

what is the rule for choosing to and from ?

[sunny_talwar]

May be this

Table:
LOAD * INLINE [
    Supplier, Fecha
    A, 01/01/2020 03:45
    A, 01/01/2020 05:30
    B, 01/01/2020 06:56
    A, 01/01/2020 09:00
    A, 01/01/2020 10:34
    B, 01/01/2020 11:23
];

tmpTable:
LOAD DISTINCT Supplier,
	 If(Supplier = Previous(Supplier), Previous(Fecha), Fecha) as From
Resident Table
Order By Fecha;

Table:
LOAD DISTINCT Supplier,
	 From,
	 Alt(Peek('From'), '12/31/9999 11:59') as To
Resident tmpTable
Order By From Desc;

DROP Table Table, tmpTable;

[Brett_Bleess]

Did Sunny's example get you what you needed?  If so, please do not forget to return to your thread and use the Accept as Solution button on his post if so, that gives him credit for the assistance and it also lets the other Community Members know it did work too.  If you still need further help, leave an update post.

Regards,
Brett

[Saravanan_Desingh]

One solution is.

tab1:
LOAD *, If(Peek('Supplier')=Supplier, Peek('K1'), RangeSum(Peek('K1'),1)) As K1
	  ,	Peek('K1') As K2
;
LOAD Supplier, Timestamp#(Fecha,'M/D/YYYY hh:mm') As Fecha INLINE [
    Supplier, Fecha
    A, 1/1/2020 3:45
    A, 1/1/2020 5:30
    B, 1/1/2020 6:56
    A, 1/1/2020 9:00
    A, 1/1/2020 10:34
    B, 1/1/2020 11:23
];

Left Join (tab1)
LOAD K1, TimeStamp(Min(Fecha)) As From
Resident tab1
Group By K1
;

tab2:
LOAD K2, TimeStamp(Max(Fecha)) As To
Resident tab1
Group By K2
;

Left Join(tab1)
LOAD K2 As K1, To 
Resident tab2;

Drop Table tab2;

[Saravanan_Desingh]