topic Intervalmatch but keep all data in both tables in QlikView

Intervalmatch but keep all data in both tables

richard_chilvers — Wed, 10 Oct 2018 08:43:37 GMT

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.

Re: Intervalmatch but keep all data in both tables

swuehl — Wed, 10 Oct 2018 08:55:59 GMT

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

Re: Intervalmatch but keep all data in both tables

richard_chilvers — Wed, 10 Oct 2018 09:26:56 GMT

Hi Stefan

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

Also my results are unexpected:

Re: Intervalmatch but keep all data in both tables

swuehl — Wed, 10 Oct 2018 10:14:20 GMT

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

Re: Intervalmatch but keep all data in both tables

richard_chilvers — Wed, 10 Oct 2018 10:27:31 GMT

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;

Re: Intervalmatch but keep all data in both tables

swuehl — Wed, 10 Oct 2018 11:24:42 GMT

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.

Re: Intervalmatch but keep all data in both tables

richard_chilvers — Wed, 10 Oct 2018 12:34:48 GMT

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

Re: Intervalmatch but keep all data in both tables

swuehl — Wed, 10 Oct 2018 12:56:00 GMT

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;

Re: Intervalmatch but keep all data in both tables

richard_chilvers — Wed, 10 Oct 2018 13:17:07 GMT

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

Thanks