topic Matching postcodes in data by timeframe (NHS) in QlikView

Matching postcodes in data by timeframe (NHS)

Wed, 11 May 2011 10:35:49 GMT

Hi,

Hoping someone can help me.

I have a dataset that contains postcodes and a timestamp when the record was created I ideally need to be able to say if there is another entry for this postcode within a 24hr period.

e.g.

postcode - xx22 4xx time - 00:00:00

postcode - xx22 4xx time - 01:00:00

I would like to be able to count this entry as a match as it occured within a 24hr period. I am not sure how to do this as they will be on different records in the databse.

Really appreciate any help people can give me.

Thanks

Matching postcodes in data by timeframe (NHS)

IAMDV — Wed, 11 May 2011 11:13:44 GMT

Please can you post a QV document with sample data? It will help us to provide some ideas...

Cheers - DV

Re: Matching postcodes in data by timeframe (NHS)

Wed, 11 May 2011 12:15:09 GMT

Hi,

Thanks for having a look at this. I have attached a Qlikview document with a table that contains incident number, postcode and time clock start. Where there are multiple entries for a postcode within 24hrs of each other I would like to do a count of these.

Thanks for your help

Re: Matching postcodes in data by timeframe (NHS)

IAMDV — Thu, 12 May 2011 08:25:57 GMT

Quick question:

Lets assume you have some data like this...

IncidentNumber	Postcode	TimeClockStart
S001	XX22 4XX	01/04/2011 00:00:00 .
S002	XX22 4XX	01/04/2011 05:00:00 .
S005	XX22 4XX	01/04/2011 22:00:00 .
S006	XX22 4XX	01/06/2011 18:00:00 .
S007	XX22 4XX	01/08/2011 22:00:00 .
S008	XX22 4XX	01/09/2011 20:00:00 .

Now do you want to count the postcodes within last 24 hours (0 in this example) or do you want to count the time difference between each postcode and if it is less than 24hrs then count them up (5 in this example).

I am assuming that you want the later! however, I wanted to make sure before I go ahead and try something on this.

Cheers - DV

Re: Matching postcodes in data by timeframe (NHS)

Thu, 12 May 2011 08:52:12 GMT

Hi,

You are correct it is the latter open that we would like to achieve

"do you want to count the time difference between each postcode and if it is less than 24hrs then count them up (5 in this example)."

Thank you for looking at this for me.

Viv

Re: Matching postcodes in data by timeframe (NHS)

IAMDV — Thu, 12 May 2011 14:17:22 GMT

Hi Viv,

Sorry, I could not provide you complete solution. But here is what I had tried so far...

I had created 2 variables :

vPostCode : ONLY({$} Postcode)

vMinTime : MIN({1< Postcode = {'$(=($(vPostCode)))'}>} TimeClockStart)

Now vMinTime variable gives me the lowest timestamp for the postcode selected. Then I thought I could use the chart to substract the

(TimeStamp - $(=($(vMinTime))) ) to get the difference for each TimeStamp across all the postcode. Next I thought I could use the Count of (TimeStamp - $(=($(vMinTime))) ) <= 1 (1day). However, my variable is calculating for each dimension inspite of specify {1} for the TimeStamp.

Probably I need to think in different approach. I'll come back to you with alternate solution. Meanwhile can someone look into this please?

Also attaching my version of QV document.

Good luck!

Cheers - DV