Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
j0nimost1
Contributor II
Contributor II
‎2020-07-04 06:38 AM

If Condition Incorrect result

I am getting incorrect results when using the if statement to determine the dimension for a bar graph.

The script in question is as shown below;

=if(Benefit_Limit < 50000 , 'BELOW-50K',
if(Benefit_Limit >= 50000 and Benefit_Limit < 100000, '50-100K LIMIT',
if(Benefit_Limit >= 100000 and Benefit_Limit < 300000, '100-300 LIMIT',
if(Benefit_Limit >= 300000 and Benefit_Limit < 500000, '300-500 LIMIT',
if(Benefit_Limit >= 500000 and Benefit_Limit < 1000000, '500K-1M LIMIT', 'Over 1M')))))

 

There are values for each range but I'm getting the following output.

j0nimost1_0-1593858962742.png

I have not limited the width or anything of the sort. I can't figure out the reason for this behavior. Kindly assist



Labels (2)
Labels
832 Views
0 Likes
7 Replies
Kushal_Chawda
MVP
MVP
‎2020-07-04 07:39 AM

what is the expression you are using?

798 Views
0 Likes
j0nimost1
Contributor II
Contributor II
‎2020-07-06 12:01 AM
Author

In response to Kushal_Chawda

It's in the question-- but here you go;

 

=if(Benefit_Limit < 50000 or IsNull(Benefit_Limit), 'BELOW-50K',
if(Benefit_Limit >= 50000 and Benefit_Limit < 100000, '50-100K LIMIT',
if(Benefit_Limit >= 100000 and Benefit_Limit < 300000, '100-300 LIMIT',
if(Benefit_Limit >= 300000 and Benefit_Limit < 500000, '300-500 LIMIT',
if(Benefit_Limit >= 500000 and Benefit_Limit < 1000000, '500K-1M LIMIT', 'Over 1M')))))

748 Views
0 Likes
PrashantSangle
MVP
MVP
‎2020-07-06 12:39 AM

In response to j0nimost1

Hi,

 

What is your dimension an measure?? Convert this chart into table and check. How you are testing the value that it is correct or not?

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
739 Views
0 Likes
tresesco
MVP
MVP
‎2020-07-06 12:58 AM

In response to PrashantSangle

It is perhaps something not related to this expression. The potential issue could be with your data. If you could share a sample data has the issue - we may have a look and try to help. In the mean while, you can simplify expression like:

=if(Benefit_Limit < 50000 or IsNull(Benefit_Limit), 'BELOW-50K',
if(Benefit_Limit >= 50000 and Benefit_Limit < 100000, '50-100K LIMIT',
if(Benefit_Limit >= 100000 and Benefit_Limit < 300000, '100-300 LIMIT',
if(Benefit_Limit >= 300000 and Benefit_Limit < 500000, '300-500 LIMIT',
if(Benefit_Limit >= 500000 and Benefit_Limit < 1000000, '500K-1M LIMIT', 'Over 1M')))))

729 Views
0 Likes
j0nimost1
Contributor II
Contributor II
‎2020-07-06 08:27 AM
Author

In response to tresesco

Sample Data; in the database it's saved as nvarchar

Benefit Limit 

1000000
1000000
1000000
50000
50000
50000
100000
100000
50000
NULL
50000
50000
35000
100000
50000
50000
1000000
1000000
100000
75000
75000
50000
100000
100000
100000
100000
50000
100000
100000
50000
50000
50000
100000
100000
100000
100000
50000
35000
50000
50000
50000
50000
1000000
50000
50000
50000
50000
50000
50000
50000
50000
30000
50000
75000
50000
50000
50000
75000
5000000
75000
35000
35000
35000
75000
75000
35000
35000
50000
50000
50000
35000
50000
50000
100000
100000
100000
100000
50000
75000
75000
75000
75000
50000
50000
50000
50000
50000
50000
75000
75000
75000
50000
50000
100000
100000
150000
75000
NULL
500000
500000
75000
75000
75000
75000
75000
50000
50000
50000
50000
150000
150000
150000
150000
75000
75000
100000
100000
100000
100000
500000
500000
10000000
10000000
100000
2500000
NULL
500000
500000
35000
75000
75000
50000
50000
50000
130000
35000
75000
100000
75000
75000
50000
50000
20000
50000
50000
50000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
100000
100000
100000
100000
150000
100000
100000
100000
100000
100000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
100000
100000
100000
150000
75000
75000
75000
150000
150000
150000
150000
150000
150000
100000
100000
150000
150000
150000
150000
150000
150000
75000
150000
150000
75000
75000
75000
75000
75000
75000
75000
75000
75000
300000
300000
150000
150000
150000
50000
75000
75000
75000
75000
75000
75000
75000
75000
50000
75000
75000
75000
75000
75000
50000
50000
100000
50000
35000
35000
50000
35000
35000
50000
50000
35000
35000
35000
35000
100000
100000
100000
100000
70000
100000
35000
35000
35000
35000
100000
100000
100000
100000
50000
100000
100000
12000
100000
50000
50000
50000
50000
150000
150000
50000
50000
200000
150000
200000
200000
50000
150000
150000
150000
150000
35000
200000
200000
100000
200000
200000
150000
150000
150000
150000
150000
150000
50000
75000
150000
50000
150000
35000
50000
50000
200000
150000
150000
150000
35000
200000
200000
50000
150000
35000
150000
150000
150000
150000
50000
50000
50000
50000
50000
150000
150000
150000
150000
150000
200000
150000
150000
150000
50000
35000
150000
200000
150000

667 Views
0 Likes
j0nimost1
Contributor II
Contributor II
‎2020-07-06 09:10 AM
Author

In response to tresesco

Benefit Limits from the database are nvarchar

 

Here is a sample;

1000000
1000000
1000000
50000
50000
50000
100000
100000
50000
NULL
50000
50000
35000
100000
50000
50000
1000000
1000000
100000
75000
75000
50000
100000
100000
100000
100000
50000
100000
100000
50000
50000
50000
100000
100000
100000
100000
50000
35000
50000
50000

699 Views
0 Likes
tresesco
MVP
MVP
‎2020-07-06 09:37 AM

In response to j0nimost1

With my simplified expression for your sample data I am getting like:

=if(Benefit_Limit < 50000 or Benefit_Limit='NULL', 'BELOW-50K',
if(Benefit_Limit < 100000, '50-100K LIMIT',
if(Benefit_Limit < 300000, '100-300 LIMIT',
if(Benefit_Limit < 500000, '300-500 LIMIT',
if(Benefit_Limit < 1000000, '500K-1M LIMIT', 'Over 1M')))))

tresesco_0-1594042604572.png

 

686 Views
0 Likes