IntervalMatch dates & key

[fks]

Hi,

I have the following two tables:

[SALES]:
LOAD * Inline [
PERIOD, TEAM, SALES
2016-09-01, A, 400
2016-09-01, B, 300
2016-10-01, B, 200
2016-11-01, A, 150
2016-11-01, B, 425
2016-12-01, A, 340
2016-12-01, B, 100
2017-01-01, A, 10
2017-01-01, B, 40
2017-02-01, A, 95
2017-02-01, B, 155
2017-03-01, A, 510
2017-03-01, B, 74
2017-04-01, A, 51
];

[TEAMS]:
LOAD * Inline [
TEAM, PERIOD-START, PERIOD-END, PRODUCT
A, 2000-01-01, 2016-12-31, 'Jam'
A, 2017-01-01, 2020-12-31, 'Spoon'
B, 1999-01-01, 2016-11-30, 'Shark'
B, 2016-12-01, 2022-12-31, 'Tuna'
];

The TEAM-field links the two tables, but since the PRODUCT field (of the TEAMS-table) is dependent upon the intervals and PERIOD (of the SALES-table) the PRODUCT is showing invalid results.

Hence I would like to join the PRODUCT field to the SALES-table, and with IntervalMatch get the right PRODUCT at every PERIOD.
By dropping the TEAMS-table the preferred result would then look like this:

PERIOD, TEAM, SALES, PRODUCT

2016-12-31, A, 340, 'Jam'
2017-01-01, A, 10, 'Spoon'

I've attached a qvf with the sample data.

Thanks!

[Vegar]

You  confused me a bit with your expected result table, as you do not have any transaction PERIOD 2016-12-31. 

However I tried to understand your problem and I believe that you could use the extended syntax of intervalmatch.

Please look att the code below.

[SALES]:
LOAD * Inline [ 
PERIOD, TEAM, SALES
   2016-09-01, A, 400
   2016-09-01, B, 300
   2016-10-01, B, 200
   2016-11-01, A, 150
   2016-11-01, B, 425
   2016-12-01, A, 340
   2016-12-01, B, 100
   2017-01-01, A, 10
   2017-01-01, B, 40
   2017-02-01, A, 95
   2017-02-01, B, 155
   2017-03-01, A, 510
   2017-03-01, B, 74
   2017-04-01, A, 51
];

[TEAMS]:
LOAD * Inline [ 
   TEAM, PERIOD-START, PERIOD-END, PRODUCT
   A, 2000-01-01, 2016-12-31, 'Jam'
   A, 2017-01-01, 2020-12-31, 'Spoon'
   B, 1999-01-01, 2016-11-30, 'Shark'
   B, 2016-12-01, 2022-12-31, 'Tuna'
];


LEFT JOIN (SALES)
IntervalMatch(PERIOD, TEAM) 
LOAD 
   [PERIOD-START], 
   [PERIOD-END], 
   TEAM 
RESIDENT TEAMS
;

[HirisH_V7]

could you please explain, what is the logic or requirement for getting only these alone from all the mapped cases once interval match is done.

PERIOD, TEAM, SALES, PRODUCT

2016-12-31, A, 340, 'Jam'
2017-01-01, A, 10, 'Spoon'

Anyway we can map product using apply-map for a period if required. pl tell above.

[fks]

Thanks for the replies.

It's my typo, the resulting table would be in the structure PERIOD, TEAM, SALES, PRODUCT.
The two lines being just example output (and more confusing the first line had a wrong date..).

Vegar's code did the trick, however if I could get rid of the TEAMS-table and the synthetic key it would be even better (the actual database have several similar linked tables/intervals so if possible I would like to keep the data model as simple as possible).