Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted

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]

Highlighted

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

Highlighted
MVP & Luminary
MVP & Luminary

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.

Highlighted
Creator
Creator

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