topic Re: scripting issue in QlikView

scripting issue

samuel_brierley — Fri, 31 Jul 2015 08:16:44 GMT

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.

Re: scripting issue

Fri, 31 Jul 2015 08:25:36 GMT

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.

Re: scripting issue

MayilVahanan — Fri, 31 Jul 2015 08:29:30 GMT

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;

Re: scripting issue

qlikviewwizard — Fri, 31 Jul 2015 08:36:18 GMT

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.

Re: scripting issue

samuel_brierley — Fri, 31 Jul 2015 08:44:23 GMT

Hi yes not a problem

thanks

Re: scripting issue

jonathandienst — Fri, 31 Jul 2015 08:50:02 GMT

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;

Re: scripting issue

jonathandienst — Fri, 31 Jul 2015 08:53:44 GMT

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

Re: scripting issue

samuel_brierley — Fri, 31 Jul 2015 09:22:11 GMT

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.