Match two table

[#1]

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
 ]; 

[hic]

See IntervalMatch

HIC

[#3]

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]

Hi,

Henric is right. Use interval Match.

[#5]

[hic]

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;