Intervalmatch but keep all data in both tables

[richard_chilvers]

Hi

I have 2 tables of data about sales and telephone calls.

The first table (Calls) has all the calls made for every customer, giving the start and end call date/time.

CustomerID, Call Start, Call End

The second table (Sales) has all sales which are made, and the date/time of the sale.

CustomerID, SalesDateTime

Of course, not all calls end up with a sale. Also sales can be made in-store and not by making a call.

I want to match sales to calls where relevant, but keep ALL the data in both tables even where there is no match.

What would be the neatest method ?

Thanks in advance.

[swuehl]

If you use Intervalmatch LOAD prefix to create table that links your two tables, isn't your requirement fulfilled by design?

[richard_chilvers]

Hi Stefan

This has been my approach so far, but I am concerned because I get 3 synthetic keys

Also my results are unexpected:

[swuehl]

How does your script look like? Could you post a small sample QVW?

[richard_chilvers]

Hi - this is a snip of my test QVW (sorry for the simulated call start & end):

Calls:
LOAD * INLINE [
CallStart, CallEnd, CustomerID
1, 2, G1
3, 4, G1
2.3, 2.4, G2
5, 6, G3
10,12,G3
3, 4, G5
2.9, 6, G7
];

Sales:

LOAD * INLINE [

Sold, CustomerID
3.5, G5
7.1, G2
5.5, G3
17, G4

];

Matches:

IntervalMatch(Sold, CustomerID)
LOAD CallStart, CallEnd, CustomerID RESIDENT Calls;



EXIT SCRIPT;

[swuehl]

You probably need just

Matches:

JOIN(Calls)

IntervalMatch(Sold, CustomerID)

LOAD CallStart, CallEnd, CustomerID RESIDENT Calls;

You can also get rid of the synthetic key in another step, but it shouldn't really bother you.

[richard_chilvers]

Oh yes - that's looking nice, and I don't mind one synthetic key

[swuehl · Accepted solution]

If you can have multiple sales per call (Oh, Buy 2 get 3!), the JOIN can lead to duplication of facts in your calls table.

Another option would be to not JOIN to your fact tables, but create a real linking table like this:

Calls:

LOAD *, Recno() as CallID INLINE [

CallStart, CallEnd, CustomerID

1, 2, G1

3, 4, G1

2.3, 2.4, G2

5, 6, G3

10,12,G3

3, 4, G5

2.9, 6, G7

];

Sales:

LOAD *, Recno() as SalesID INLINE [

Sold, CustomerID

3.5, G5

7.1, G2

5.5, G3

5.7, G3

17, G4

];

Matches:

//JOIN(Calls)

IntervalMatch(Sold, CustomerID)

LOAD CallStart, CallEnd, CustomerID RESIDENT Calls;

JOIN (Matches)

LOAD CallStart, CallEnd, CustomerID, CallID

Resident Calls;

JOIN (Matches)

LOAD Sold, CustomerID, SalesID

Resident Sales;

DROP FIELDS CallStart, CallEnd, Sold FROM Matches;

DROP Field CustomerID FROM Sales;

DROP FIELD CustomerID FROM Calls;

EXIT SCRIPT;

[richard_chilvers]

Yes, that's nice too because in my 'real' data I already have unique IDs for calls and for sales.

Thanks