1 Reply Latest reply: Apr 26, 2012 5:13 PM by Stefan Wühl RSS

    need expression help for nth match with restrictions

      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.

        • Re: need expression help for nth match with restrictions
          Stefan Wühl

          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.

           

          see also attached,

          Stefan

           

          P.S. This is not selection sensitive.