Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
richard_chilvers
Specialist
Specialist

Intervalmatch but keep all data in both tables

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

8 Replies
swuehl
MVP
MVP

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

richard_chilvers
Specialist
Specialist
Author

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
MVP
MVP

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

richard_chilvers
Specialist
Specialist
Author

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
MVP
MVP

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
Specialist
Specialist
Author

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

swuehl
MVP
MVP

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
Specialist
Specialist
Author

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

Thanks