Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an equipment table with start date, end date and equipment status. The dates are sequential (end date from one row is equal to the start of the next row).
I have this following table:
Table1:
Load * inline
[
Equipment,Start,End,Status
A,1,2,2
A,2,3,2
A,3,4,2
A,4,5,2
A,5,6,3
A,6,7,3
A,7,8,2
A,8,9,2
];
What I am trying to do is combine rows and get:
Table2:
Load * inline
[
Equipment,Start,End,Status
A,1,5,2
A,5,7,3
A,7,9,2
];
I tried to use:
if(previous(status) = status,previous(start),start) as start
but it works only with 2 sequential rows
and we cant use group by min and max because we will lost the third line A,7,9,2
Thanks in advance
Perhaps like this:
Table1:
Load
*,
If(Status<>Previous(Status), Rangesum(Peek(Group),1),Peek(Group)) as Group
inline
[
Equipment,Start,End,Status
A,1,2,2
A,2,3,2
A,3,4,2
A,4,5,2
A,5,6,3
A,6,7,3
A,7,8,2
A,8,9,2
];
Table2:
NOCONCATENATE LOAD
Equipment,
min(Start) as Start,
max(End) as End,
Status,
Group
RESIDENT
Table1
GROUP BY
Equipment,
Status,
Group
;
DROP FIELD Group;
DROP TABLE Table1;
Perhaps like this:
Table1:
Load
*,
If(Status<>Previous(Status), Rangesum(Peek(Group),1),Peek(Group)) as Group
inline
[
Equipment,Start,End,Status
A,1,2,2
A,2,3,2
A,3,4,2
A,4,5,2
A,5,6,3
A,6,7,3
A,7,8,2
A,8,9,2
];
Table2:
NOCONCATENATE LOAD
Equipment,
min(Start) as Start,
max(End) as End,
Status,
Group
RESIDENT
Table1
GROUP BY
Equipment,
Status,
Group
;
DROP FIELD Group;
DROP TABLE Table1;
you are a life saver!!
thanks a lot
I did it in a far more complex/less elegant way:
Table1:
Load * inline
[
Equipamento,Start,End,Status
A,1,2,2
A,2,3,2
A,3,4,2
A,4,5,2
A,5,6,3
A,6,7,3
A,7,8,2
A,8,9,2
];
Table2:
LOAD
*,
RowNo() as aux
Resident Table1;
Drop table Table1;
Table3:
LOAD *,
if(IsNull(
if(aux = aux1,previous(aux)/Previous(aux1),aux/aux1)),
1,
if(aux = aux1,previous(aux)/Previous(aux1),aux/aux1)
)
as aux2;
LOAD *,
if(Previous(Equipamento) = Equipamento and Previous(Status) = Status,Previous(aux)+1,1) as aux1
Resident Table2;
Drop table Table2;
NoConcatenate
Table4:
LOAD
Equipamento, Start, Status, aux2
Resident Table3
where aux1 = 1;
left join(Table4)
LOAD
Equipamento,
Status,
aux2,
max(End) as End
Resident Table3
group by Equipamento, Status, aux2;
Drop field aux2;
Drop table Table3;