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

Turn on suggestions

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

- 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

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

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

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

662 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

662 Views

Community Browser