7 Replies Latest reply: Dec 15, 2016 8:05 PM by Gus Feltham RSS

    help with grouping Min value timestamp and where clause

    Gus Feltham

      I need to create a load as below that groups by Ref_number for the earliest timestamp, but also with a where clause

      that Callsign must =Comms.     I also need to exclude NULL timestamps.

       

      something like:

       

      A:

      load "Ref_Number",

          Date(min(timestamp),'DD/MM/YY hh:mm') as first_at_scene

      from [lib://QlikSense server general data depository (corp_gusfe)/ServiceOps.qvd]

      (qvd)

      group by "Ref_Number";

       

      but when I add a where clause (where Callsign = 'Comms') , it also returns:

      BX39   5/12/2016 15:40  Comms   ??   (I only want the min timestamps if they are also Callsign='Comms')

       

      note the actual data includes 'ss' in timestamp and there are no instances of the same timestamp for the same Ref_Number

      - unless the are Nulls which I don't want considered.

       

       

      data available:

      Ref_numbertimestampCallsign

       

      BX39

      5/12/2016 15:30Alpha
      BX395/12/2016 15:40Comms
      BX406/12/2016 15:30Beta
      BX406/12/2016 15:27Comms
      BX428/12/2016 15:30Alpha
      BX439/12/2016 15:30Beta
      BX439/12/2016 15:25Comms
      BX4310/12/2016 15:23Comms
      BX4411/12/2016 15:30Beta
      output table required:
      Ref_numbertimestampCallsignflag
      BX406/12/2016 15:27CommsY
      BX439/12/2016 15:23CommsY

       

      This table will then join with a main table by Ref_number to give me a flag indicator to use in charts and filter lists within the App.

       

      many thanks

        • Re: help with grouping Min value timestamp and where clause
          Sunny Talwar

          May be this:

           

          Table:

          LOAD *

          Where Callsign = 'Comms';

          LOAD Ref_number,

            TimeStamp(Min(timestamp)) as timestamp,

            FirstSortedValue(Callsign, timestamp) as Callsign

          Group By Ref_number;

          LOAD * INLINE [

              Ref_number, timestamp, Callsign,

              BX39, 5/12/2016 15:30, Alpha

              BX39, 5/12/2016 15:40, Comms

              BX40, 6/12/2016 15:30, Beta

              BX40, 6/12/2016 15:27, Comms

              BX42, 8/12/2016 15:30, Alpha

              BX43, 9/12/2016 15:30, Beta

              BX43, 9/12/2016 15:25, Comms

              BX43, 10/12/2016 15:23, Comms

              BX44, 11/12/2016 15:30, Beta

          ];

            • Re: help with grouping Min value timestamp and where clause
              Gus Feltham

              Thanks Sunny.

              I was using a simplified example in my question, but my actual  script now seems to be in the wrong order as it comes up with an error that the

               

              Please help me get the order of all these loads correct!

              There may even be a better way ? – I simply want to end up with a flag indicating if (Callsign=’Comms’ & and was the earliest timestamp for a Ref_Number  -  that I can use in my chart set analysis and drop down lists with the App.

              Thanks

              Gus

               

               

               

              //New part of script from Sunny

              Fire:

              load *

              where Callsign =’Comms’;

               

              load Ref_Number,

              timestamp(min("First_unit_arr")) as first_unit,

              FirstSortedValue(Callsign, "First_unit_arr") as Callsign

              group by Ref_Number;

               

               

               

              // script already in place and working okay

              Temp:

              CrossTable(Journey_part, Duration, 23)

              LOAD

              Distinct "Ref_Number",

              "Incident_ID",

              "Problem_Description",

              "Source_Address",

              "Destination_Location_Name",

              Callsign,

              "Assign_Performed_By",

              Shift & ' Shift' as Shift_Name,

              "Source_City" as Incident_location,

              Capitalize(Colour) as Colour, //note this is initial priority

              //Capitalize("Priority_Description_Final") as ,// final priority

              Date1,

              Division,

              IF("Destination_Location_Name"='WRHED',"Destination_Location_Name",

                    IF("Destination_Location_Name"='WRHED',"Destination_Location_Name",

                    IF ("Destination_Location_Name"='WPH ED',"Destination_Location_Name",

                    IF ("Destination_Location_Name"='HHED',"Destination_Location_Name",

                    IF ("Destination_Location_Name"='Wairarapa ED',"Destination_Location_Name",

                    'Other'))))) AS Dest_ED,

              if("Weekday"='Mon',dual('Mon',1),if("Weekday"='Tue',dual('Tue',2),if("Weekday"='Wed',dual('Wed',3),if("Weekday"='Thu',dual('Thu',4),if("Weekday"='Fri',dual('Fri',5),

              if("Weekday"='Sat',dual('Sat',6),if("Weekday"='Sun',dual('Sun',7)))))))) as ,

              year(Date1)&'-'&week(Date1)as WkYr,

              "Time Interval(rounded down)",

              date(date#("Mth-Yr",'MMM-YY'),'MMM-YY')as "Mth-Yr",

              Date("Callentered_Queue",'DD/MM/YY hh:mm') as ,

              Date("First_unit_alerted",'DD/MM/YY hh:mm') as "First_unit_alerted",

              Date("First_unit_enroute",'DD/MM/YY hh:mm') as "First_unit_enroute",

              Date("First_unit_arr",'DD/MM/YY hh:mm') as "First_unit_arr",

              Date("Arr_hosp",'DD/MM/YY hh:mm') as "Arr_hosp",

              Date("Dep_hosp",'DD/MM/YY hh:mm')as "Dep_hosp",

              interval(interval#(Assign,'ss'),'mm:ss') as  Assign,

              interval(interval#(OOS,'ss'),'mm:ss') as OOC,

              interval(interval#("Hospital turnaround",'ss'),'mm:ss') AS ,

              interval(interval#("Overall Response",'ss'),'mm:ss') AS ,

              interval(interval#("Complete Journey",'ss'),'mm:ss')  AS

               

              from

              (qvd)

              where 1=1;

               

               

              :

              Load *,

               

              Duration as Dur2,

              if(Duration<=maketime(00,06,00),'<6mins',

              if(Duration<=maketime(00,08,00),'<8mins',

              if(Duration<=maketime(00,08,00) and Duration>maketime(00,06,00),dual('6-8mins',1),

              if(Duration<=maketime(00,10,00) and Duration>maketime(00,08,00),dual('8-10mins',2),

              if(Duration<=maketime(00,12,00) and Duration>maketime(00,10,00),dual('10-12mins',3),

              if(Duration<=maketime(00,14,00) and Duration>maketime(00,12,00),dual('12-14mins',4),

              if(Duration<=maketime(00,16,00) and Duration>maketime(00,14,00),dual('14-16mins',5),

              if(Duration<=maketime(00,18,00) and Duration>maketime(00,16,00),dual('16-18mins',6),

              if(Duration<=maketime(00,20,00) and Duration>maketime(00,18,00),dual('18-20mins',7),

              dual('>20mins',8)))))))))) as time_interval,

               

              if(Duration<=maketime(00,08,00),'<8mins',

              if(Duration<=maketime(00,10,00) ,'8-10mins',

              if(Duration<=maketime(00,12,00) ,'10-12mins',

              if(Duration<=maketime(00,14,00) ,'12-14mins',

              if(Duration<=maketime(00,16,00) ,'14-16mins',

              if(Duration<=maketime(00,18,00) ,'16-18mins',

              if(Duration<=maketime(00,20,00) ,'18-20mins',

              '>20mins')))))))as time_interval1,

               

               

              if(Duration<=maketime(00,08,00),dual('<8mins',1),

              if(Duration<=maketime(00,10,00) and Duration>maketime(00,08,00),dual('8-10mins',2),

              if(Duration<=maketime(00,12,00) and Duration>maketime(00,10,00),dual('10-12mins',3),

              if(Duration<=maketime(00,14,00) and Duration>maketime(00,12,00),dual('12-14mins',4),

              if(Duration<=maketime(00,16,00) and Duration>maketime(00,14,00),dual('14-16mins',5),

              if(Duration<=maketime(00,18,00) and Duration>maketime(00,16,00),dual('16-18mins',6),

              if(Duration<=maketime(00,20,00) and Duration>maketime(00,18,00),dual('18-20mins',7),

              dual('>20mins',8)))))))) as time_interval2,

              if(Duration<maketime(01,00,00),'<1hr',0) as time_interval3

              resident Temp

              ;

               

              drop table Temp;

            • Re: help with grouping Min value timestamp and where clause
              Sunny Talwar

              Not sure this will work because of the timestamp in there, but try this:

               

              // script already in place and working okay

              Temp:

              CrossTable(Journey_part, Duration, 23)

              LOAD

              Distinct "Ref_Number",

              "Incident_ID",

              "Problem_Description",

              "Source_Address",

              "Destination_Location_Name",

              Callsign,

              "Assign_Performed_By",

              Shift & ' Shift' as Shift_Name,

              "Source_City" as Incident_location,

              Capitalize(Colour) as Colour, //note this is initial priority

              //Capitalize("Priority_Description_Final") as ,// final priority

              Date1,

              Division,

              IF("Destination_Location_Name"='WRHED',"Destination_Location_Name",

                    IF("Destination_Location_Name"='WRHED',"Destination_Location_Name",

                    IF ("Destination_Location_Name"='WPH ED',"Destination_Location_Name",

                    IF ("Destination_Location_Name"='HHED',"Destination_Location_Name",

                    IF ("Destination_Location_Name"='Wairarapa ED',"Destination_Location_Name",

                    'Other'))))) AS Dest_ED,

              if("Weekday"='Mon',dual('Mon',1),if("Weekday"='Tue',dual('Tue',2),if("Weekday"='Wed',dual('Wed',3),if("Weekday"='Thu',dual('Thu',4),if("Weekday"='Fri',dual('Fri',5),

              if("Weekday"='Sat',dual('Sat',6),if("Weekday"='Sun',dual('Sun',7)))))))) as ,

              year(Date1)&'-'&week(Date1)as WkYr,

              "Time Interval(rounded down)",

              date(date#("Mth-Yr",'MMM-YY'),'MMM-YY')as "Mth-Yr",

              Date("Callentered_Queue",'DD/MM/YY hh:mm') as ,

              Date("First_unit_alerted",'DD/MM/YY hh:mm') as "First_unit_alerted",

              Date("First_unit_enroute",'DD/MM/YY hh:mm') as "First_unit_enroute",

              Date(Round("First_unit_arr", 1440), 'DD/MM/YY hh:mm') as "First_unit_arr",

              Date("Arr_hosp",'DD/MM/YY hh:mm') as "Arr_hosp",

              Date("Dep_hosp",'DD/MM/YY hh:mm')as "Dep_hosp",

              interval(interval#(Assign,'ss'),'mm:ss') as  Assign,

              interval(interval#(OOS,'ss'),'mm:ss') as OOC,

              interval(interval#("Hospital turnaround",'ss'),'mm:ss') AS ,

              interval(interval#("Overall Response",'ss'),'mm:ss') AS ,

              interval(interval#("Complete Journey",'ss'),'mm:ss')  AS

              FROM (qvd)

              where 1=1;

               

              Right Join (Temp)

              LOAD *

              Where Callsign =’Comms’;

              LOAD Ref_Number,

              Min("First_unit_arr") as "First_unit_arr"

              Resident Temp

              Group By Ref_Number;

               

              FinalTable:

              LOAD *,

              Duration as Dur2,

              if(Duration<=maketime(00,06,00),'<6mins',

              if(Duration<=maketime(00,08,00),'<8mins',

              if(Duration<=maketime(00,08,00) and Duration>maketime(00,06,00),dual('6-8mins',1),

              if(Duration<=maketime(00,10,00) and Duration>maketime(00,08,00),dual('8-10mins',2),

              if(Duration<=maketime(00,12,00) and Duration>maketime(00,10,00),dual('10-12mins',3),

              if(Duration<=maketime(00,14,00) and Duration>maketime(00,12,00),dual('12-14mins',4),

              if(Duration<=maketime(00,16,00) and Duration>maketime(00,14,00),dual('14-16mins',5),

              if(Duration<=maketime(00,18,00) and Duration>maketime(00,16,00),dual('16-18mins',6),

              if(Duration<=maketime(00,20,00) and Duration>maketime(00,18,00),dual('18-20mins',7),

              dual('>20mins',8)))))))))) as time_interval,

               

              if(Duration<=maketime(00,08,00),'<8mins',

              if(Duration<=maketime(00,10,00) ,'8-10mins',

              if(Duration<=maketime(00,12,00) ,'10-12mins',

              if(Duration<=maketime(00,14,00) ,'12-14mins',

              if(Duration<=maketime(00,16,00) ,'14-16mins',

              if(Duration<=maketime(00,18,00) ,'16-18mins',

              if(Duration<=maketime(00,20,00) ,'18-20mins',

              '>20mins')))))))as time_interval1,

               

               

              if(Duration<=maketime(00,08,00),dual('<8mins',1),

              if(Duration<=maketime(00,10,00) and Duration>maketime(00,08,00),dual('8-10mins',2),

              if(Duration<=maketime(00,12,00) and Duration>maketime(00,10,00),dual('10-12mins',3),

              if(Duration<=maketime(00,14,00) and Duration>maketime(00,12,00),dual('12-14mins',4),

              if(Duration<=maketime(00,16,00) and Duration>maketime(00,14,00),dual('14-16mins',5),

              if(Duration<=maketime(00,18,00) and Duration>maketime(00,16,00),dual('16-18mins',6),

              if(Duration<=maketime(00,20,00) and Duration>maketime(00,18,00),dual('18-20mins',7),

              dual('>20mins',8)))))))) as time_interval2,

              if(Duration<maketime(01,00,00),'<1hr',0) as time_interval3

              Resident Temp;

               

              DROP Table Temp;

               

              note the changes in red....