topic Re: If statment with a lower and upper limit in QlikView

If statment with a lower and upper limit

isciberras — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I was hoping to pick your brains to understand why the following code does not work

I have a column with multiple values which I would like to categorise into buckets according to value.

if(num(value) < num(0) and num(value) >= num(10), '<£10',
if(num(value) <= num(10) and num(value) >= num(50), '£10 - £50',
if(num(value) <= num(50) and num(value) >= num(100), '£50 - £100',
if(num(value) <= num(100) and num(value) >= num(499), '£100 - £499',
if(num(value) <= num(500) and num(value) >= num(999), '£500 - £999',
if(num(value) <= num(1000) and num(value) >= num(5000), '£1k - £5k',
if(num(value) <= num(5000) and num(value >= num(10000), '£5k - £10k',
if(num(value) <= num(10000) and num(value) >= num(50000), '£10k - £50k',
if(num(value) <= num(50000) and num(value) >= num(100000), '£50k - £100k',
if(num(value) <= num(100000) and num(value) >= num(500000), '£100k - £500k',
if(num(value) <= num(1000000) and num(value) >= num(10000000), '1M – 10M',
if(num(value) > num(10000000) and num(value >= num(900000000000000000000000000), '>10m')))))))))))) as [SumGroupings]

However the result is usually '>10m' even though the column value has a vast range of values from very small to very large figures.

Would anyone happen to know why this is happening?

Thanks,
Isaac

Re: If statment with a lower and upper limit

sunny_talwar — Fri, 17 Aug 2018 14:31:09 GMT

I think you < and >= signs are incorrect... try this

if(num(value) > num(0) and num(value) <= num(10), '<£10',
if(num(value) > num(10) and num(value) <= num(50), '£10 - £50',
if(num(value) > num(50) and num(value) <= num(100), '£50 - £100',
if(num(value) > num(100) and num(value) <= num(499), '£100 - £499',
if(num(value) > num(500) and num(value) <= num(999), '£500 - £999',
if(num(value) > num(1000) and num(value) <= num(5000), '£1k - £5k',
if(num(value) > num(5000) and num(value) <= num(10000), '£5k - £10k',
if(num(value) > num(10000) and num(value) <= num(50000), '£10k - £50k',
if(num(value) > num(50000) and num(value) <= num(100000), '£50k - £100k',
if(num(value) > num(100000) and num(value) <= num(500000), '£100k - £500k',
if(num(value) > num(1000000) and num(value) <= num(10000000), '1M – 10M',
if(num(value) > num(10000000) and num(value) <= num(900000000000000000000000000), '>10m')))))))))))) as [SumGroupings]

Re: If statment with a lower and upper limit

sunny_talwar — Fri, 17 Aug 2018 14:33:54 GMT

And you might be able to do away with num() functions all together

if(value > 0 and value <= 10, '<£10',

if(value > 10 and value <= 50, '£10 - £50',

if(value > 50 and value <= 100, '£50 - £100',

if(value > 100 and value <= 499, '£100 - £499',

if(value > 500 and value <= 999, '£500 - £999',

if(value > 1000 and value <= 5000, '£1k - £5k',

if(value > 5000 and value <= 10000, '£5k - £10k',

if(value > 10000 and value <= 50000, '£10k - £50k',

if(value > 50000 and value <= 100000, '£50k - £100k',

if(value > 100000 and value <= 500000, '£100k - £500k',

if(value > 1000000 and value <= 10000000, '1M – 10M',

if(value > 10000000 and value <= 900000000000000000000000000, '>10m')))))))))))) as [SumGroupings]

Re: If statment with a lower and upper limit

marcus_sommer — Fri, 17 Aug 2018 14:47:13 GMT

Another and even easier and more performant approach would be to use a pick(match()) for it like:

= pick(match(-1, value < 10, value < 50, ...), '<10', '<50', ...)

- Marcus

ps: the origin idea to compare conditions against TRUE is from Sunny.

Re: If statment with a lower and upper limit

isciberras — Fri, 17 Aug 2018 15:15:36 GMT

You're right, it was my greater than smaller than logic that was incorrect. Thank you for your help, wish I asked this earlier instead of spending days trying to figure out why its not working