Issue with IntervalMatch

[#1]

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.

[johnw]

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.

[#3]

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

[#4]

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'];

[johnw]

You can't just inner join a table with ranges and have an intervalmatch occur automatically.  You have to tell QlikView that you want to create an intervalmatch, and tell it what your range fields are, and what field you need to compare the range to.  In other words, you need to use the intervalmatch syntax in order to do an intervalmatch.

[#6]

Sorry, my mistake, i did put the intervalmatch syntax on the QVW script, (corrected previous post); as i said with the new code 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

[johnw · Accepted solution]

See attached working example.

[#8]

Thanks a Lot!! will try to implement it on my QVW

[#9]

Worked