Matching postcodes in data by timeframe (NHS)

[#1]

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

[IAMDV]

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

Cheers - DV

[#3]

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

[IAMDV]

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

[#5]

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

[IAMDV]

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