topic Re: Need Help in QlikView

Need Help

Thu, 28 May 2015 03:51:08 GMT

Good evening, I am new to Qlik (started using it today). I am using the desktop version, and had some help from an acquaintance setting up my first app and it is working great. I am using the program as an alternative to one we were using and has since been shut down. I am a member of a fire department and we are using to analyze our response times and are in the process of gathering the data for a presentation to our city administration in about 2 weeks. I am looking at records pulled out of our records management system and am trying to figure out how to drill into the data a little differently.

We currently have pulled out our incidents and have them filtered by incident number and then we are showing the units that have responded to the call and all of the corresponding times that we need for the report. A lot of the incidents have multiple units that have responded to the call (some as many as 15-20), however I only need to look at the times associated with the first few units that respond. For example, I have incident number 12345 and units 101, 102, 103 and 104 have responded on the call each with different times. I need to be able to just look at the times for the unit that arrived on scene first, this would be easy to figure out if it was just one incident, however I am looking at thousands of incidents and need to apply this to all of them in a range.

So, is there any way to write a formula or something that will allow me to review times based on the order the units responded?

Thank you for your help

David Lentz

Re: Need Help

pokassov — Thu, 28 May 2015 07:17:06 GMT

Hello David!

The solution depends on you model.

For example if you have one fact table with incident number and unit and datetime field then you could do this

t2:

noconcatenate

load

incident,

unit,

datetime,

if(previos(incident)<>incident,1,peek('unit_order')) as unit_order

resident your_table

order by

incident,

datetime;

drop your_table;

Now you can build table using set_analysis. For example:

dimention:

unit

expression:

count ({$<unit_order={1}>} incident)

Re: Need Help

Thu, 28 May 2015 11:48:53 GMT

Thank you for the reply, unfortunately I am very new to this whole thing with a very basic knowledge. So basically I am not exactly sure what you are saying.

Would either of the examples you gave be used in the Qlik desktop or would those be excel formulas, not exactly sure where to use them or put them.

Re: Need Help

maxgro — Thu, 28 May 2015 19:42:13 GMT

// some test data (if you can post your Qlik doc (.qvw) we can use your real data)

TestTable:

load

floor(rand()*100)+1 as Incident,

'Unit ' & floor(rand()*100) as Unit,

date(makedate(2015)+rand()*100 + rand()/(24*60)) as DateTime

AutoGenerate

1000;

Table:

load

Incident,

// this is the order for the same incident

if(peek(Incident)<>Incident, 1, peek(RespOrder)+1) as RespOrder,

Unit,

DateTime

Resident TestTable

Order by Incident, DateTime, Unit;

DROP Table TestTable;

now you can use RespOrder to filter the first 2 or 3 or n units