Combine rows with start date and end date

[igorgois_]

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

[Gysbert_Wassenaar · Accepted solution]

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;

[igorgois_]

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;