Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

intervalmatch use

Hello guys

I'm trying to find solution with intervalmatch but I think I'm missing something...

I have 2 tables:

   

ClientsByDates
MonthClientKey
02-2013100
03-2013100
04-2013100
02-2013101
03-2013101
04-2013101
02-2013102
03-2013102
04-2013

102

 

   

ClientsLevelels
FromMonthToMonthClientKeyLevel
                        02-201303-2013    1001
04-201307-2013    1004
02-201303-2013     991
04-201307-2013      992

 

As a result I want to get the Client Level from second table in the first one by month:

   

ClientsByDates
MonthClientKeyLevel
02-20131001
03-20131001
04-20131004
02-2013101-
03-2013101-
04-2013101-
02-2013102-
03-2013102-
04-2013102-
992
991

Thanks in advance for your help

Tags (1)
1 Solution

Accepted Solutions

Re: intervalmatch use

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:

Double field intervalmatch thread200696.jpg

10 Replies

Re: intervalmatch use

May be this?

Capture.PNG

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;

manojkulkarni
Valued Contributor II

Re: intervalmatch use

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

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

Re: intervalmatch use

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

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

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

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:

Double field intervalmatch thread200696.jpg

Re: intervalmatch use

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

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.