Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
[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.
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;