Combine value only once with another value

Tue, 17 Nov 2015 12:01:09 GMT

I want to reduce a table by combining two lines into one where there is a connection. I have the following data

Id, Next, Begin, End

1, 2, 08:30, 10:00

1, 2, 15:20, 16:40

2, , 17:00, 18:30,

2, , 19:20, 21:00

Each entry in "next" has exactly ONE corresponding in "id", for example the the first and the third line belong together and the second and the forth. The minimal difference between the end-time of the first and the begin time of the second makes clear, which entry must be picked if one id occurs several times. But one value should only be connected once, so line 1 has a waiting time of 7 hours before line 3 begins. and though there is only a difference of 20 minutes between line 2 and 3, line 3 could not be used a second time and instead there is a waiting time of 2 hours and 40 minutes.

but right now i could not restrict it that way and therefore line 3 is connected to line 1 and line 2 which i do not intend.

can anyone help?

Re: Combine value only once with another value

swuehl — Tue, 17 Nov 2015 23:51:16 GMT

Maybe like this:


[Data]:
LOAD * INLINE [
    Id, Next, Begin, End
    1, 2, 08:30, 10:00
    1, 2, 15:20, 16:40
    2, , 17:00, 18:30
    2, , 19:20, 21:00
];


[Temp_Combine]:
LOAD Id as TId,
  End as TEnd,
  Next as TCId
RESIDENT [Data];


LEFT JOIN([Temp_Combine])
LOAD
  Id as TCId,
  Begin as TNextBegin
RESIDENT [Data]
WHERE EXISTS(Id);




[Temp_Difference]:
LOAD TId,
  TEnd,
  TCId,
  TNextBegin,
  If(IsNull(TCId), 0, Time(TNextBegin - TEnd, 'hh:mm')) as [Wait]
RESIDENT [Temp_Combine]
WHERE TNextBegin - TEnd >= 0;
DROP TABLE [Temp_Combine];


[Waiting]:
LOAD TId, TEnd, TCId, TNextBegin, Wait, TEnd as Check1, TNextBegin as Check2
RESIDENT [Temp_Difference]
Where Not Exists(Check1, TEnd) and Not Exists(Check2, TNextBegin)
Order by TId, TEnd, Wait;


drop table [Temp_Difference];

topic Combine value only once with another value in QlikView

Combine value only once with another value

Re: Combine value only once with another value