need expression help for nth match with restrictio... - Qlik Community

Report Inappropriate Content · ‎2012-04-26

Hi, trying to craft an expression to get a result into a chart table.

I have a data table listing arrival times for vehicles to incidents (the attached qvw illustrates). Each vehicle has a type Pumper, Ladder or Rescue. I need to determine when benchmark X has been met. Benchmark X is the first time when three pumpers have arrived OR two pumpers and one ladder have arrived.

Struggling. I can get the three pumpers part in isolation, I use the "firstsortedvalue" function and 3 as the third parameter filtering on type. The second part is tricky and I can't think of how to start.

swuehl · ‎2012-04-26

Maybe you could create a field which holds a composite of arrival time for all pumpers and for the first ladder per incident.

Like:

TMP:

LOAD FirstSortedValue(RowID, ArrivalTime) as RowID, FirstSortedValue(ArrivalTime,ArrivalTime) as PL resident Arrival where Type = 'Ladder' group by Incident;

LOAD RowID, ArrivalTime as PL Resident Arrival where Type='Pumper';

Result:

left join (Arrival) LOAD * Resident TMP;

drop table TMP;

Then do your firstsortedvalue(PL,PL,3) in the chart.

need expression help for nth match with restrictions