Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Match two table

I need to apply these sales discounts given date sales count them . Can anyone help me?
Discount:
LOAD * INLINE [
Customer, Star Date, End Date, Discount
1, 01/01/2015, 31/12/2015, 5
1, 01/02/2015, 31/12/2015, 4
2, 01/01/2015, 31/01/2015, 2
3, 01/01/2015, 31/01/2015, 5
4, 01/06/2015, 30/06/2015, 10
4, 01/01/2015, 31/12/2015, 5
]
;

Sales:
LOAD * INLINE [
Customer, SalesDate, Amount
1, 01/01/2015, 150
2, 01/05/2015, 98
3, 01/01/2015, 75
4, 10/06/2015, 157
1, 01/02/2015, 140
2, 28/01/2015, 80
3, 31/05/2015, 210
4, 20/04/2015, 150
]
;
5 Replies
hic
Former Employee
Former Employee

See IntervalMatch

HIC

Not applicable
Author

Hi Henric,

If I use this function I'm doubling my sales.


Discount:
LOAD * INLINE [
Customer, Start Date, End Date, Discount
1, 01/01/2015, 31/12/2015, 5
1, 01/02/2015, 31/12/2015, 4
2, 01/01/2015, 31/01/2015, 2
3, 01/01/2015, 31/01/2015, 5
4, 01/06/2015, 30/06/2015, 10
4, 01/01/2015, 31/12/2015, 5
]
;

Sales:
LOAD * INLINE [
Customer, SalesDate, Amount
1, 01/01/2015, 150
2, 01/05/2015, 98
3, 01/01/2015, 75
4, 10/06/2015, 157
1, 01/02/2015, 140
2, 28/01/2015, 80
3, 31/05/2015, 210
4, 20/04/2015, 150
]
;
Left Join IntervalMatch (SalesDate,Customer)
 
LOAD
[Start Date],
[End Date],
Customer
RESIDENT Discount;
Left Join  LOAD * RESIDENT Discount;
 
DROP TABLE Discount;

hariprasadqv
Creator III
Creator III

Hi,

Henric is right. Use interval Match.

Not applicable
Author

hic
Former Employee
Former Employee

Yes you need the extended intervalmatch. See Slowly Changing Dimensions

This script should do the trick:

Discount:
LOAD *, Customer & '|' & [Star Date] & '|' & [End Date] as CustomerIntervalID
INLINE [
Customer, Star Date, End Date, Discount
1, 01/01/2015, 31/12/2015, 5
1, 01/02/2015, 31/12/2015, 4
2, 01/01/2015, 31/01/2015, 2
3, 01/01/2015, 31/01/2015, 5
4, 01/06/2015, 30/06/2015, 10
4, 01/01/2015, 31/12/2015, 5]
;

Sales:
LOAD *, Customer & '|' & [SalesDate] as CustomerDateID
INLINE [
Customer, SalesDate, Amount
1, 01/01/2015, 150
2, 01/05/2015, 98
3, 01/01/2015, 75
4, 10/06/2015, 157
1, 01/02/2015, 140
2, 28/01/2015, 80
3, 31/05/2015, 210
4, 20/04/2015, 150]
;

tmpIntervalMatch:
IntervalMatch (SalesDate, Customer)
Load [Star Date], [End Date], Customer
Resident Discount;

IntervalMatch:
Load
Customer & '|' & [Star Date] & '|' & [End Date] as CustomerIntervalID,
Customer & '|' & [SalesDate] as CustomerDateID
Resident tmpIntervalMatch;

Drop Table tmpIntervalMatch;
Drop Field Customer From Sales;