Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Match two table

See IntervalMatch

HIC

Not applicable

Re: Match two table

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
Contributor III

Re: Match two table

Hi,

Henric is right. Use interval Match.

Not applicable

Re: Match two table

Re: Match two table

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;