topic Re: intervalmatch use in QlikView

intervalmatch use

Wed, 20 Jan 2016 08:25:17 GMT

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

Re: intervalmatch use

sunny_talwar — Wed, 20 Jan 2016 09:51:34 GMT

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;

Re: intervalmatch use

manojkulkarni — Wed, 20 Jan 2016 09:51:37 GMT

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;

Re: intervalmatch use

Peter_Cammaert — Wed, 20 Jan 2016 10:06:38 GMT

Why do you want the lines for client 99 to be present in the resulting table? Nothing matches for this client.

Re: intervalmatch use

Peter_Cammaert — Wed, 20 Jan 2016 10:12:59 GMT

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

Re: intervalmatch use

Peter_Cammaert — Wed, 20 Jan 2016 10:18:16 GMT

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.

Re: intervalmatch use

sunny_talwar — Wed, 20 Jan 2016 10:19:38 GMT

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

Re: intervalmatch use

Peter_Cammaert — Wed, 20 Jan 2016 10:27:44 GMT

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:

Re: intervalmatch use

sunny_talwar — Wed, 20 Jan 2016 10:41:29 GMT

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.

Re: intervalmatch use

Peter_Cammaert — Wed, 20 Jan 2016 11:32:58 GMT

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.

Re: intervalmatch use

Thu, 21 Jan 2016 08:35:26 GMT

Thanks

I decided not to present client99 (thanks to you) and your solution works great