Discussion board where members can get started with QlikView.
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:
Load * inline
What I am trying to do is combine rows and get:
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
Go to Solution.
Perhaps like this:
If(Status<>Previous(Status), Rangesum(Peek(Group),1),Peek(Group)) as Group
min(Start) as Start,
max(End) as End,
DROP FIELD Group;
DROP TABLE Table1;
View solution in original post
you are a life saver!!
thanks a lot
I did it in a far more complex/less elegant way:
RowNo() as aux
Drop table Table1;
if(aux = aux1,previous(aux)/Previous(aux1),aux/aux1)),
if(aux = aux1,previous(aux)/Previous(aux1),aux/aux1)
if(Previous(Equipamento) = Equipamento and Previous(Status) = Status,Previous(aux)+1,1) as aux1
Drop table Table2;
Equipamento, Start, Status, aux2
where aux1 = 1;
max(End) as End
group by Equipamento, Status, aux2;
Drop field aux2;
Drop table Table3;