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

IntervalMatch dates & key

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!

Labels (3)
3 Replies
Vegar
MVP
MVP

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
Master
Master

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.
HirisH
“Aspire to Inspire before we Expire!”
fks
Contributor III
Contributor III
Author

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).