Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
guster999
Creator
Creator
‎2016-12-13 06:23 PM

help with grouping Min value timestamp and where clause

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

523 Views
0 Likes
7 Replies
sunny_talwar
MVP
MVP
‎2016-12-13 06:51 PM

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

];

411 Views
0 Likes
guster999
Creator
Creator
‎2016-12-13 09:40 PM
Author

In response to sunny_talwar

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;

411 Views
0 Likes
guster999
Creator
Creator
‎2016-12-13 10:46 PM
Author

In response to guster999

apologies - that latest post from me should have read at the top:

"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 <Duration field was no found> "

cheers

Gus


411 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-12-14 04:13 AM

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....

411 Views
0 Likes
guster999
Creator
Creator
‎2016-12-14 03:23 PM
Author

In response to sunny_talwar

Thanks Sunny,

I made the reran the script as per what you posted, but still getting an error:

The following error occurred:

Field not found -

The error occurred here:

LOAD Ref_Number, Min("First_unit_arr") as "First_unit_arr" Resident Temp Group By Ref_Number

Thanks

Gus

411 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-12-14 06:17 PM

In response to guster999

Would you be able to share your application to take a look at the script?

411 Views
0 Likes
guster999
Creator
Creator
‎2016-12-15 08:05 PM
Author

In response to sunny_talwar

Hi Sunny,

I have restricted the data to Dec16 only as per attached.

Note that my load script has an initial qvd load section, then a main load from qvd section (which is what I had been posting).

Thinking more about it - the best result would be to get a flag created as per criteria discussed, but in the initial qvd load section - that way I can also use this new field for other Apps that feed off this same qvd.

many thanks

Gus

Service Delivery Ops - for fire flag testing.qvf
ServiceOps.qvd
411 Views
0 Likes