Copy next Timestamp with condition

[atair]

Hello friends.

I have a scenario where I need to calculate the duration of different status of devices. Status could be working, disturbance, stopped. 

All timestamps are recorded in on column. The challenge for was that when the status is Working, i.e. End.Time of a disturbance, there is a different ID.  Here there is an example of the table and the expected result:

Sample:

Status	Device	Time.Stamp	ID	Attribute	Value
Working	aa1	20190412205455	2	Reason	Fixed
Disturbance	aa1	20190412153848	1	Reason	broken
Disturbance	aa1	20190412153848	1	Fault	Engine
Working	aa2	20190408175554	4	Reason	Fixed
Disturbance	aa2	20190408174658	3	Reason	broken
Disturbance	aa2	20190408174658	3	Fault	PC
Working	aa1	20190402185803	6	Reason	Fixed
Stopped	aa1	20190402185151	5	Reason	broken
Working	aa2	20190412205455	7	Reason	Fixed
Disturbance	aa2	20190412153848	6	Reason	broken
Disturbance	aa2	20190412153848	6	Fault	Panel
Working	aa1	20190408175554	8	Reason	Fixed
Disturbance	aa1	20190408174658	9	Reason	broken
Disturbance	aa1	20190408174658	9	Fault	Engine
Working	aa2	20190402185803	11	Reason	Fixed
Stopped	aa2	20190402185151	10	Reason	broken

Expected:

Status	Device	Time.Stamp	ID	Attribute	Value
Working	aa1	20190412205455	2	Reason	Fixed
Disturbance	aa1	20190412153848	1	End.Time	20190412205455
Disturbance	aa1	20190412153848	1	Reason	broken
Disturbance	aa1	20190412153848	1	Fault	Engine
Working	aa2	20190408175554	4	Reason	Fixed
Disturbance	aa2	20190408174658	3	End.Time	20190408175554
Disturbance	aa2	20190408174658	3	Reason	broken
Disturbance	aa2	20190408174658	3	Fault	PC
Working	aa1	20190402185803	6	Reason	Fixed
Stopped	aa1	20190402185151	5	End.Time	20190402185803
Stopped	aa1	20190402185151	5	Reason	broken
Working	aa2	20190412205455	7	Reason	Fixed
Disturbance	aa2	20190412153848	6	End.Time	20190412205455
Disturbance	aa2	20190412153848	6	Reason	broken
Disturbance	aa2	20190412153848	6	Fault	Panel
Working	aa1	20190408175554	8	Reason	Fixed
Disturbance	aa1	20190408174658	9	End.Time	20190408175554
Disturbance	aa1	20190408174658	9	Reason	broken
Disturbance	aa1	20190408174658	9	Fault	Engine
Working	aa2	20190402185803	11	Reason	Fixed
Stopped	aa2	20190402185151	10	End.Time	20190402185803
Stopped	aa2	20190402185151	10	Reason	broken

Thanks in advance

[lfetensini · Accepted solution]

Well, we need a lot work to do this... Lets Go...

// Extract Phase with Row Number
[TMP DATA]:
LOAD
Status,
Device,
Num(Time.Stamp) as Time.Stamp,
Num(ID) as ID,
Attribute,
Trim(Value) as Value,
RowNo() as RowID
FROM Test.xlsx(ooxml, embedded labels, table is Planilha1);

// Getting broken and fixed value for ID
NoConcatenate
[TMP NEW DATA]:
LOAD
Status,
Device,
Time.Stamp,
ID,
Attribute,
Value,
RowID
Resident [TMP DATA]
Where Match(Lower(Value),'fixed', 'broken')
Order By RowID;

// Doing End.Time
NoConcatenate
[END DATA]:
LOAD
Status,
Device,
Time.Stamp,
ID,
If(Lower(Value)='broken', 'End.Time', Attribute) as Attribute,
If(Lower(Value)='broken', Previous(Time.Stamp), Value) as Value,
RowID
Resident [TMP NEW DATA]
Order By RowID;

DROP Table [TMP NEW DATA];

// Inserting End.Time
Concatenate([TMP DATA])
LOAD
Status,
Device,
Time.Stamp,
ID,
Attribute,
Value,
RowID
Resident [END DATA]
Where Attribute = 'End.Time';

DROP Table [END DATA];

// Sorting new Table (OPTIMAL):

NoConcatenate

[DATA]:
LOAD
Status,
Device,
Time.Stamp,
ID,
Attribute,
Value
Resident [TMP DATA]
Order By
RowID;

DROP Tables [TMP DATA];