10 Replies Latest reply: Jan 21, 2016 3:36 AM by eva f RSS

    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

        • Re: intervalmatch use
          Sunny Talwar

          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;

            • Re: intervalmatch use
              Peter Cammaert

              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
                  Sunny Talwar

                  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

                      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
                  Manoj Kulkarni

                  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

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