8 Replies Latest reply: Jul 12, 2011 6:04 PM by jclozanoc RSS

    Issue with IntervalMatch

      I'm developing a qlikview report where i want to use intervalmatch to allow selection of ranges, after checking the documentation included with the software i managed to get to this code

      TableThatNeedsIntervalMatch:

      LOAD autonumber(Text(keyfield1 & '-' & keyfield2 & '-' & keyfield3    & '-' & keyfield4 & '-' & keyfield5)) as bigkeyfield1,

      moneyValue1,

      if(Exists(moneyValue2,"<$(moneyValue1)"),'Algorithm','Preset')

      FROM Load\Table1.qvd(qvd);

      JOIN(TableWithIntervalMatch)

      LOAD autonumber(Text(keyfield1 & '-' & keyfield2 & '-' & keyfield3 & '-' & keyfield4    & '-' & keyfield5)) as bigkeyfield1,

      autonumber(Text(keyfield1 & '-' & keyfield2 & '-' & keyfield3 & '-' & keyfield4     & '-' & keyfield6)) as bigkeyfield2,

      keyfield6 as kncp,

      keyfield1 as kucf,

      keyfield2 as kpcf,

      keyfield3 as kgcf,

      keyfield4 as kccf

      FROMLoad\Table2.qvd(qvd);

      RangeForIntervalMatch:

      LOAD * INLINE

      [   

      moneyValue1Min, moneyValue1Max, NameMoneyValue1   

      Num(0),  Num(49.99), 'up to 49.99'   

      Num(50),  Num(99.99), 'between 50 and 99.99'   

      Num(100), Num(149.99), 'between 10 and 149.99'   

      Num(150), Num(199.99), 'between 150 and 199.99'   

      Num(200), Num(249.99), 'between 200 and 249.99'   

      Num(250), Num(299.99), 'between 250 and 99.99'   

      Num(300), Num(349.99), 'between 300 and 349.99'   

      Num(350), Num(399.99), 'between 350 and 399.99'   

      Num(400), null(), '400 and above'];

      ResidentTableWithTheIntervalMatch:

      inner join(TableThatNeedsIntervalMatch)

      IntervalMatch(moneyValue1)

      Load

      moneyValue1Min,   

      moneyValue1Max,   

      NameMoneyValue1   

      Resident RangeForIntervalMatch;

      Drop Table RangeForIntervalMatch;

      However, after loading; it doesn't shows any error nor it loads the Information that should be on the intervalMatch, the main table loads but the secondary one with the ranges doesn't.

        • Re: Issue with IntervalMatch
          John Witherspoon

          I believe that RangeForIntervalMatch literally has values like 'Num(249.99)' in it instead of numbers, preventing them from functioning as ranges.  To make sure I'm right, just don't drop the table and have a look at what's there.  Just put the numbers themselves there.  For null(), just leave the field blank.  That said, I think I remember having trouble with nulls in intervalmatch in the past, and have resorted to putting in huge numbers instead.

            • Issue with IntervalMatch

              Thanks for the suggestion, will try tomorrow and report back with what happened ^_^

              • Re: Issue with IntervalMatch

                It Was Happening as you said, it gets a "NUM(50)" as text however when removing it it just deletes the interval table as if it didn't exist: all the fields including shown as unavailable; the table does not show on the diagram

                 

                I changed the code to this:

                 

                TableThatNeedsIntervalMatch:

                LOAD autonumber(Text(keyfield1 & '-' & keyfield2 & '-' & keyfield3    & '-' & keyfield4 & '-' & keyfield5)) as bigkeyfield1,

                moneyValue1,

                if(Exists(moneyValue2,"<$(moneyValue1)"),'Algorithm','Preset')

                FROM Load\Table1.qvd(qvd);

                JOIN(TableWithIntervalMatch)

                LOAD autonumber(Text(keyfield1 & '-' & keyfield2 & '-' & keyfield3 & '-' & keyfield4    & '-' & keyfield5)) as bigkeyfield1,

                autonumber(Text(keyfield1 & '-' & keyfield2 & '-' & keyfield3 & '-' & keyfield4     & '-' & keyfield6)) as bigkeyfield2,

                keyfield6 as kncp,

                keyfield1 as kucf,

                keyfield2 as kpcf,

                keyfield3 as kgcf,

                keyfield4 as kccf

                FROMLoad\Table2.qvd(qvd);

                 

                RangeForIntervalMatch:

                inner join(TableThatNeedsIntervalMatch)

                IntervalMatch(moneyValue1)

                LOAD * INLINE 

                moneyValue1Min, moneyValue1Max, NameMoneyValue1  

                  0,  49.99, 'up to 49.99'  

                50,  99.99, 'between 50 and 99.99'  

                100, 149.99, 'between 10 and 149.99'  

                150, 199.99, 'between 150 and 199.99'  

                200, 249.99, 'between 200 and 249.99'  

                250, 299.99, 'between 250 and 99.99'  

                300, 349.99, 'between 300 and 349.99'  

                350, 399.99, 'between 350 and 399.99'  

                400, 99999999999, '400 and above'];