topic Re: Issue with IntervalMatch in QlikView

Issue with IntervalMatch

Thu, 07 Jul 2011 16:30:19 GMT

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

johnw — Thu, 07 Jul 2011 21:23:46 GMT

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

Tue, 12 Jul 2011 03:59:41 GMT

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

Re: Issue with IntervalMatch

Tue, 12 Jul 2011 17:01:52 GMT

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

Re: Issue with IntervalMatch

johnw — Tue, 12 Jul 2011 18:59:39 GMT

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.

Re: Issue with IntervalMatch

Tue, 12 Jul 2011 20:23:26 GMT

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

Re: Issue with IntervalMatch

johnw — Tue, 12 Jul 2011 21:05:18 GMT

See attached working example.

Re: Issue with IntervalMatch

Tue, 12 Jul 2011 21:12:25 GMT

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

Re: Issue with IntervalMatch

Tue, 12 Jul 2011 22:04:27 GMT

Worked