topic Combine rows with start date and end date in QlikView

Combine rows with start date and end date

igorgois_ — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Combine rows with start date and end date

Gysbert_Wassenaar — Thu, 10 Aug 2017 16:20:04 GMT

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;

Re: Combine rows with start date and end date

igorgois_ — Thu, 10 Aug 2017 17:29:27 GMT

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)),

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;