Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I don't actually think this is possible but hey ho there are far brighter people out there than I.
hopefully the below should try and explain what im trying to do. (Ive not been careful with any Syntax)
Load
Asset ID,
Fault ID,
Raised Time,
Cleared Time,
Fault Re-Occurrence
where the first 4 fields exist in my data source
Fault Re-Occurance is a field that looks down to see If the Asset has failed again with the same Fault ID. if it has put the time difference(Raised time of second fault minus cleared time of first fault) if it hasn't just put an X for completeness.
thanks guys this will really help.
Hi,
Can you provide an example data of what Asset ID, Fault ID, Raised Time and Cleared Time look like.
Also an example using data as to what you want to acheive. Using real data in numbers or days, etc.
Hi
I think your expecting something like this
Load *, If(AssetID = Previous(AssetID) and FaultID = Previous(FaultID), RaisedTime - Previous(ClearedTime), 'X') as
FaultReOccurrence;
Load
AssetID,
FaultID,
RaisedTime,
ClearedTime
From Datasource
Order by Asset ID, Fault ID;
Hi
Can you give sample data with the application. So that it will be very easy to understand your issue and give best solution. Thank You.
Hi yes not a problem
thanks
This should work. The time is computed from the cleared time of the first instance of the fault, even if there are more than two failures:
LOAD *,
If([Asset ID] = Previous([Asset ID]) And [Fault ID] = Previous([Fault ID]), [Raised Time] - FirstClearedTime) As [Fault Re-Occurrence] |
;
LOAD
[Asset ID], | |
[Fault ID], | |
[Raised Time], | |
[Cleared Time], | |
If([Asset ID] <> Previous([Asset ID]) Or [Fault ID] <> Previous([Fault ID]), [Cleared Time], Peek(FirstClearedTime)) As FirstClearedTime |
FROM .....
Order By [Asset ID], [Fault ID], [Raised Time];
DROP Field FirstClearedTime;
I am worried about the Previous in the preceding load portion. If the above script does not work, try this:
LOAD
[Asset ID],
[Fault ID],
[Raised Time],
[Cleared Time],
If(RecurFlag = 1, [Raised Time] - FirstClearedTime) As [Fault Re-Occurrence]
;
LOAD
[Asset ID],
[Fault ID],
[Raised Time],
[Cleared Time],
If([Asset ID] <> Previous([Asset ID]) Or [Fault ID] <> Previous([Fault ID]), [Cleared Time], Peek(FirstClearedTime)) As FirstClearedTime,
If([Asset ID] = Previous([Asset ID]) And [Fault ID] = Previous([Fault ID]), 1, 0) As RecurFlag
FROM .....
Order By [Asset ID], [Fault ID], [Raised Time];
Hi all,
Im actually more confused now than i was when i started
maybe a better requirement would be to simply return the last Clearance time where the Asset ID and Fault ID are the same.
so the function will look up all previous loaded data if it finds a record with the same Asset ID and Fault ID return the Clearance Time of that record.