Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;