Dates between rows - Matching ID & Time - Interval/Peek/Min [Script][Sense]

[coco832]

Hi,

I am trying to get the state duration for the next table:I try to create a script where i can peek the minimum date where the resource ID match and the date is older than the one from the row, then I get the timestamp between the two dates. But I am getting an error. There my sample & Error (sorry about SPA):

SQL (guideline): select Min(eventdatetime) from agentstatedetail where agentstatedetail.resourceID equal resourceID And agentstatedetail.eventdatetime >= eventdatetime;

Anyone knows what I am missing? Error code is as useful as windows problem solver. I am not able to modify the data source.

[dwforest]

Need a Group By for the Min()

[coco832]

[agentstatedetailB]:
LOAD
	[resourceid],
	[agentstatedetail.eventdatetime],
	Interval(if(Peek([resourceid])=[resourceid] And Peek([agentstatedetail.eventdatetime])>[agentstatedetail.eventdatetime],Min([agentstatedetail.eventdatetime]))-[agentstatedetail.eventdatetime]) AS [Duration] 
    Resident agentstatedetail Group By [agentstatedetail.eventdatetime]
    ;

Still getting the error.

[coco832 · Accepted solution]

After few day i was not able to find a direct solution using Qlik Script functions, there you got  a solution with SQL:

This is an inaccurate solution.

SQL SELECT mt.agentid,
	mt.eventdatetime,
	mt2.eventdatetime AS nexteventdatetime,
	mt2.eventdatetime - mt.eventdatetime AS stateduration
  FROM (
SELECT
	sd.agentid,
	sd.eventdatetime,
	ROW_NUMBER() OVER( ORDER BY sd.agentid ASC, sd.eventdatetime ASC) AS this_row_nr,
	( ROW_NUMBER() OVER( ORDER BY sd.agentid ASC, sd.eventdatetime ASC) ) + 1 AS next_row_nr
  FROM 
	informix.agentstatedetail	AS sd
) AS mt
  LEFT JOIN
(
SELECT
	sd.agentid,
	sd.eventdatetime,
	ROW_NUMBER() OVER( ORDER BY sd.agentid ASC, sd.eventdatetime ASC) AS this_row_nr,
	( ROW_NUMBER() OVER( ORDER BY sd.agentid ASC, sd.eventdatetime ASC) ) + 1 AS next_row_nr
  FROM 
	informix.agentstatedetail	AS sd
) AS mt2
    ON mt.agentid = mt2.agentid 
   AND mt.next_row_nr = mt2.this_row_nr;