# New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
cancel
Showing results for
Did you mean:
Partner

## Combine rows with start date and end date

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:

[

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:

[

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

1 Solution

Accepted Solutions
MVP & Luminary

Perhaps like this:

Table1:

*,

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:

Equipment,

min(Start) as Start,

max(End) as End,

Status,

Group

RESIDENT

Table1

GROUP BY

Equipment,

Status,

Group

;

DROP FIELD Group;

DROP TABLE Table1;

talk is cheap, supply exceeds demand
2 Replies
MVP & Luminary

Perhaps like this:

Table1:

*,

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:

Equipment,

min(Start) as Start,

max(End) as End,

Status,

Group

RESIDENT

Table1

GROUP BY

Equipment,

Status,

Group

;

DROP FIELD Group;

DROP TABLE Table1;

talk is cheap, supply exceeds demand
Partner

you are a life saver!!

thanks a lot

I did it in a far more complex/less elegant way:

Table1:

[

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:

*,

RowNo() as aux

Resident Table1;

Drop table Table1;

Table3:

if(IsNull(

if(aux = aux1,previous(aux)/Previous(aux1),aux/aux1)),

1,

if(aux = aux1,previous(aux)/Previous(aux1),aux/aux1)

)

as aux2;

if(Previous(Equipamento) = Equipamento and Previous(Status) = Status,Previous(aux)+1,1) as aux1

Resident Table2;

Drop table Table2;

NoConcatenate

Table4:

Equipamento, Start, Status, aux2

Resident Table3

where aux1 = 1;

left join(Table4)

Equipamento,

Status,

aux2,

max(End) as End

Resident Table3

group by Equipamento, Status, aux2;

Drop field aux2;

Drop table Table3;