Hi Sindhu, the document attached returns the expected result but it has a very weak logic.
The first StartDate of an order is related to the first EndDate of that order, same for the 2nd, 3rd... For this sample of data is ok, but in real data I think you can find some issues.
Turn.qvw 150.0 K
Your logic worked perfectly
Fortunately didn't find any issues.
The method what you suggested is similar to what I used, but only the using of peek is different. I used it in another way, but didn't give proper results.
Can you please help in understanding this statement, how does it work.
If(Peek(Order)=Order, Peek(Cont)+1, 1) as Cont
It will help me in understanding more technically.
Thanks a lot...
Hi Sindhu, as you don't know wich start is related to wich end I load them in sorted by : ORDER, DateTime (Start or End).
Using that sort, if the previous loaded Order is different from the current record it means is a new order, so the Cont starts at 1 (it goes for the 'else' part of the 'if).
If the Order is equal than the previous it takes the value Cont has in previous record and adds '1'.
This way Cont is used as the ID that relate each Start with his End: Start of Order x with Cont=1 will be related with the End of the same order and the same Cont.
Just to advise again that is a very weak logic, I don't know where the data came from but for example: there will be a Start, but if there is no End (because of a blackout, deleted record, duplicated start...), the next check for that order is again a Start...something like this will break all the logic.