Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
isciberras
Creator
Creator

If statment with a lower and upper limit

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

1 Solution

Accepted Solutions
sunny_talwar

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]

View solution in original post

4 Replies
sunny_talwar

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]

sunny_talwar

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]

marcus_sommer

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.

isciberras
Creator
Creator
Author

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