Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in.
**SIGN UP NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: If statment with a lower and upper limit

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

isciberras

Creator

2018-08-17
10:26 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

766 Views

1 Solution

Accepted Solutions

sunny_talwar

MVP

2018-08-17
10:33 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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]**

4 Replies

sunny_talwar

MVP

2018-08-17
10:31 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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',**

708 Views

sunny_talwar

MVP

2018-08-17
10:33 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

MVP

2018-08-17
10:47 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2018-08-17
11:15 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

708 Views