Finding matching records within 60 minutes

[leicester]

Hi

I'm trying to find the following.

I have a dimension with two possible outcomes. Call them A and B.

For A there is a datetime. I'd like to find all records in B, which are within 60 minutes of that datetime in A.

To show them in a table and count them in a text object.

Is this easy to do?

Many thanks.

[edwin]

since this is a single field (assuming multiple rows) and all the values (both A and B) are datetime, how will the records be identified (as A or B)?

[leicester]

Hi Edwin

I have several fields for A and B and they have been allocated as A and B by adding a text field, in the script.

A and B are from separate tables.

I'm wondering whether an IntervalMatch might be the solution?

[edwin]

i will suggest the following IF they were two separate columns/dimensions and maybe you can get an idea from it.

so if you have two dimensions dimA and DimB and you want to identify all records where DimB is within 60 minutes of dimA:

load * resident TABLE where dimB>= timestamp(dimA-1/24) and dimB<= timestamp(dimA-1/24)

if dimA and dimB were in separate tables (table1 and table2 resp)

inner join (table1) load dimB residnet table2;
noconcatenate tempTable: load dimA as tempDimA, dimB as tempDimB where dimB>= timestamp(dimA-1/24) and dimB<= timestamp(dimA-1/24)

[leicester]

Thanks for the suggestions. Not sure if I can make either of them work?

I should really share an idea of what the script looks like...

Table1:
LOAD
[Date of Call] as [Date of incident],
Time([Time of Call]) as [Time of incident],
[Chief Complaint] as [Incident Type],
[Location of the injury] as [Injury Location],
[Partial Postcode] as [Postcode Sector],
'A' as [Dataset],
Time([Time of Call] + (60/(24*60))) as Add60Mins
FROM
[SomeNetworkLocation\File1.xlsx]
(ooxml, embedded labels, table is Data);

Table2:
LOAD [Occurrence Reported Date] as [Date of incident],
[Occurrence Start Time] as [Time of incident],
[HO Group Desc] as [Incident Type],
[Location Type] as [Injury Location],
PCCorrected as [Postcode Sector],
'B' as [Dataset],
Time([Occurrence Start Time] + (60/(24*60))) as Add60Mins
FROM
[SomeNetworkLocation\File2.xlsx]
(ooxml, embedded labels, table is [Data]);

Maybe don't need the Add60Mins part in the tables. I've combined both tables as one and used a text field for [Dataset] to separate them at report level. I should probably combine Date and Time, to get a proper datetime for the solution I'm looking for.

[edwin]

it appears you want to find records in Table2 that have datetime (Date of call + time of call) between same fields in table1  and +60 mins

look at this:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

data:
load Timestamp(today()+floor(rand()*200)/24) as dimA, rowno() as dimARow AutoGenerate(10); 

NoConcatenate
other:
load Timestamp(dimA+floor(rand()*100)/60/24) as dimB , rowno() as dimBRow
Resident data
while iterno()<=5;

NoConcatenate
tmpBridge:
load distinct dimA Resident data;

NoConcatenate
tmp:
load distinct dimB Resident other;

inner join (tmpBridge)
load dimB Resident tmp;

NoConcatenate
Bridge:
load dimA, dimB Resident tmpBridge where dimB>=dimA and dimB<=Timestamp(dimA+1/24);

drop tables tmp, tmpBridge;

[leicester · Accepted solution]

I've worked out a solution.

Combine the Date and Time fields together.

Did a resident load, ordered by Postcode Sector (ASC) and this date (DESC)

The following provided the answer...

If(Dataset='B', '', If(Peek(Dataset)='B',If(Peek(Datetime)-Datetime < 1/24, 'Yes', 'No'),'')) as FindNonMatching

Provides a No for each B, which had a A before it and wasn't within 60 minutes of that A. The Yes indicates there was a B within an hour of an A. For each Postcode Sector.