LOAD SCRIPT CONSUMPTION TABLE

[inigoelv]

I need the help of somebody.

I have a table of consumption like this:

Consumption	Supplier
18/03/2018 14:01:22	A
19/03/2018 22:22:05	B
21/03/2018 0:40:14	A
21/03/2018 0:40:45	B
21/03/2018 9:56:38	B
21/03/2018 11:13:18	B
21/03/2018 22:19:34	A
21/03/2018 22:21:34	A
21/03/2018 22:23:25	C
22/03/2018 2:13:10	C
22/03/2018 2:13:51	C
22/03/2018 2:15:57	A
22/03/2018 3:08:11	B
22/03/2018 7:23:29	A
22/03/2018 14:12:04	A
22/03/2018 21:59:13	B
22/03/2018 22:01:11	C

I need to group the table in a tabla of interval like this:

Initio	Fin	Supplier
18/03/2018 14:01:22	19/03/2018 22:22:05	A
19/03/2018 22:22:05	21/03/2018 0:40:14	B
21/03/2018 0:40:14	21/03/2018 0:40:45	A
21/03/2018 0:40:45	21/03/2018 22:19:34	B
21/03/2018 22:19:34	21/03/2018 22:23:25	A
21/03/2018 22:23:25	22/03/2018 2:15:57	C
22/03/2018 2:15:57	22/03/2018 3:08:11	A
22/03/2018 3:08:11	22/03/2018 7:23:29	B
22/03/2018 7:23:29	22/03/2018 21:59:13	A
22/03/2018 21:59:13	22/03/2018 22:01:11	B
22/03/2018 22:01:11		C

Thanks in advance.

I attached the excell file too.

[rubenmarin · Accepted solution]

Hi, this script cn create that table:

DataOrig:
LOAD Consumption,
Supplier
FROM
[.\Consumption.xlsx]
(ooxml, embedded labels, table is Hoja1);

tmpData:
LOAD Consumption as Initio,
If(Supplier=Peek(Supplier), Peek(Fin), Peek(Initio)) as Fin,
Supplier
Resident DataOrig order by Consumption desc;

FinalData:
NoConcatenate
LOAD *
Resident tmpData
Where Peek(Supplier)<>Supplier
Order by Initio;

DROP Table DataOrig;
DROP Table tmpData;

[inigoelv]

Many thanks.

It works perfectly.