scripting issue

[samuel_brierley]

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.

[#2]

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.

[MayilVahanan]

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;

[qlikviewwizard]

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.

[samuel_brierley]

Hi yes not a problem

thanks

[jonathandienst]

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;

[jonathandienst]

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];

[samuel_brierley]

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.