Loading two first records in each order no

[Anonymous]

Hello, 
I have a table with the following

OrderNo	OperationNo	StartDate
1175	1	2015-05-10
1175	80	2015-11-19
1175	75	2015-11-20
1175	4	2016-01-01
1024	1	2018-01-10
1024	11	2018-01-12
1024	34	2018-01-14
9876	98	2020-07-10
9876	54	2020-12-10

What I would like to do, is to calculate the time between the first operation in each order and the second operation in each order in number of days. I am not interested in keeping the other rows in the order or calculating time between them. 
Any idea on how to solve this is greatly appreciated. 
How would I extract the dates to be able to make that calculation? 
Maybe "unpivot" the table with a generic load?
Or should this calculation occur in the visualisation instead?

Cheers
Jörgen

[JordyWegman · Accepted solution]

Hi Jörgen,

This could probably be done easier, but this is solution to calculate the days between the first two record of each OrderNo.

First I create two _ind (indicators) to use for filtering. Then I load everything with reverse StartDate (desc) and only get the first two OrderNo's. Then I subtract the dates of only the first OrderNo.

Table:
Load
	*,
    IF( OrderNo <> Previous( OrderNo ),
    	1,
        IF( OrderNo = Previous( OrderNo ) and OrderNo <> Previous( Previous( OrderNo ) ),
        	1,
            0
		)
    )					as _indFirstTwo,
    IF( OrderNo <> Previous( OrderNo ),
    	1,
        0
	)					as _indFirst
;
Load * Inline [
OrderNo,	OperationNo,	StartDate
1175,	1,	2015-05-10
1175,	80,	2015-11-19
1175,	75,	2015-11-20
1175,	4,	2016-01-01
1024,	1,	2018-01-10
1024,	11,	2018-01-12
1024,	34,	2018-01-14
9876,	98,	2020-07-10
9876,	54,	2020-12-10
];


NoConcatenate
Final:
Load
	OrderNo,
    StartDate,
    EndDate,
    Date#( EndDate , 'YYYY-MM-DD' ) - Date#( StartDate , 'YYYY-MM-DD' ) as Duration
Where  _indFirst = 1
;
Load
	*,
    Previous( StartDate ) as EndDate
Resident Table
Where _indFirstTwo = 1
Order by OrderNo asc, StartDate desc
;

Drop table Table;

Jordy

Climber

[Anonymous]

I bow my head in awe!!! 
It worked perfectly! 
Thank you very much Jordy!

Cheers
/Jörgen