Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download 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