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

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.

Tags (1)
1 Solution

Accepted Solutions
johnw
Not applicable

Re: Issue with IntervalMatch

See attached working example.

8 Replies
johnw
Not applicable

Re: Issue with IntervalMatch

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.

Not applicable

Issue with IntervalMatch

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

Not applicable

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

johnw
Not applicable

Re: Issue with IntervalMatch

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.

Not applicable

Re: Issue with IntervalMatch

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
Not applicable

Re: Issue with IntervalMatch

See attached working example.

Not applicable

Re: Issue with IntervalMatch

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

Not applicable

Re: Issue with IntervalMatch

Worked