Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello guys
I'm trying to find solution with intervalmatch but I think I'm missing something...
I have 2 tables:
ClientsByDates | |
Month | ClientKey |
02-2013 | 100 |
03-2013 | 100 |
04-2013 | 100 |
02-2013 | 101 |
03-2013 | 101 |
04-2013 | 101 |
02-2013 | 102 |
03-2013 | 102 |
04-2013 | 102 |
ClientsLevelels | |||
FromMonth | ToMonth | ClientKey | Level |
02-2013 | 03-2013 | 100 | 1 |
04-2013 | 07-2013 | 100 | 4 |
02-2013 | 03-2013 | 99 | 1 |
04-2013 | 07-2013 | 99 | 2 |
As a result I want to get the Client Level from second table in the first one by month:
ClientsByDates | ||
Month | ClientKey | Level |
02-2013 | 100 | 1 |
03-2013 | 100 | 1 |
04-2013 | 100 | 4 |
02-2013 | 101 | - |
03-2013 | 101 | - |
04-2013 | 101 | - |
02-2013 | 102 | - |
03-2013 | 102 | - |
04-2013 | 102 | - |
99 | 2 | |
99 | 1 |
Thanks in advance for your help
Well, the weird thing in your table screenshot is that client 100 has two (service) levels for every single month. I think this is definitely not what the OP wants. The range match should divide levels into two distinct periods that do not overlap.
Something more selective would be:
JOIN (ClientsLevels)
INTERVALMATCH(Month,ClientKey)
LOAD FromMonth,ToMonth,ClientKey
RESIDENT ClientsLevels;
LEFT JOIN (ClientsByDates)
LOAD * RESIDENT ClientsLevels;
DROP Table ClientsLevels;
But then you would loose the 99 customer as it doesn't appear in the "pseudo"-facts table.
Just my 2cts.
Peter
[Edit]: the above gets me this:
May be this?
Table:
LOAD Date#(@1, 'MM-YYYY') as Month,
@2 as ClientKey
FROM
[https://community.qlik.com/thread/200696]
(html, codepage is 1252, no labels, table is @1, filters(
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1))
));
Table2:
LOAD Date#(@1, 'MM-YYYY') as FromMonth,
Date#(@2, 'MM-YYYY') as ToMonth,
@3 as ClientKey,
@4 as Level
FROM
[https://community.qlik.com/thread/200696]
(html, codepage is 1252, no labels, table is @2, filters(
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1))
));
IntervalMatch:
IntervalMatch(Month, ClientKey)
LOAD FromMonth,
ToMonth,
ClientKey
Resident Table2;
try something like below
LOAD * INLINE [
Month,CKey
02-2013, 100
03-2013, 100
04-2013, 100
02-2013, 101
03-2013, 101
04-2013, 101
02-2013, 102
03-2013, 102
04-2013, 102
];
Int:
LOAD * INLINE [
FromMonth,ToMonth,ClientKey, Level
02-2013, 03-2013, 100, 1
04-2013, 07-2013, 100, 4
02-2013, 03-2013, 99, 1
04-2013, 07-2013, 99, 2
];
left join (cKey)
IntervalMatch (Month,CKey)
Load distinct FromMonth, ToMonth,CKey,Level resident Int;
Why do you want the lines for client 99 to be present in the resulting table? Nothing matches for this client.
Sunny,
I think the double link between Table and Table2 (Intervalmatch synthetic key, and the straight linking by ClientKey) will produce two service levels for each matching client ID. That may be a bit too much of the good stuff.
JOIN the INTERVALMATCH to Table and drop Table2. You'll loose the 99 lines, which cannot be avoided.
Peter
Usually, ClientsByDates is some kind of facts table, to which you would like to assign a (service) level. Since there are no 99 records in this table, IMHO it serves no purpose to add the unused ranges to your facts table.
Peter Cammaert:
JOIN the INTERVALMATCH to Table and drop Table2.
I used to do that, but HIC's blog (IntervalMatch) mentioned that it might be better not to do that for efficiency reasons. So stopped doing that. But I understand there are times when you do want to join, but that would depend on individual requirements. Let me know if you think I am missing something important here.
Sunny
Well, the weird thing in your table screenshot is that client 100 has two (service) levels for every single month. I think this is definitely not what the OP wants. The range match should divide levels into two distinct periods that do not overlap.
Something more selective would be:
JOIN (ClientsLevels)
INTERVALMATCH(Month,ClientKey)
LOAD FromMonth,ToMonth,ClientKey
RESIDENT ClientsLevels;
LEFT JOIN (ClientsByDates)
LOAD * RESIDENT ClientsLevels;
DROP Table ClientsLevels;
But then you would loose the 99 customer as it doesn't appear in the "pseudo"-facts table.
Just my 2cts.
Peter
[Edit]: the above gets me this:
I am not sure why I see the those extra rows which should not be interval matched. I don't have too much experience working with Interval Match, but the few times I have used it, never saw something like this.
AFAIK, that's not too hard to figure out. Table and Table2 also connect to each other simply by way of ClientKey (single field). You end up with with two key connections (one synthetic, one regular) merged into one.