Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

How to reduce multiple if condition in script editor

Hello,

I am loading data from a table in the script editor , and in that table i have multiple if conditions, below is the code that i am using.

Could you please help me on how to reduce multiple if condition . However my below code works , but i personally do not like multiple if conditions.

Please share your thoughts.

Table2:

load  Range,

Data,

Ratio,

if(Range= '$350

to

$499', '350-499' ,if(Range= '$500

to

$749','500-749' ,if( Range='$750

to

$999','750-999',if(Range ='$1000

to

$1249','1000-1249',if(Range ='$1250

to

$1499' , '1250-1499',if(Range='$1500

to

$1749','1500-1749',if(Range='$1750

to

$1999','1750-1999',if(Range='$2000

to

$2499','2000-2499',if(Range='$2500

&

Up', '2500-Up'))))))))) as Range_num,

if(Ratio= '20% & Under','0-20',if(Ratio='22.5%',

'20.1-22.5',if(Ratio='25.0%','22.6-25.0',

if(Ratio='27.0%','25.1-27.0',if(Ratio='29.0%','27.1-29.0',

if(Ratio='31.0%','29.1-31.0',if(Ratio='33.0%','31.1-33.0',

if(Ratio='35.0%','33.1-35.0',if(Ratio='39.0%','35.1-39.0',

if(Ratio='41.0%','39.1-41.0',if(Ratio='43.0%','41.1-43.0',

if(Ratio='45.0%','43.1-45.0',if(Ratio='47.0%','45.1-47.0',

if(Ratio='50.0%','47.1-50.0',if (Ratio>='50.0%','NA'))

))))))))))))) as Range_ration

Resident Table1 ;

Thank you,

Ashis

1 Solution

Accepted Solutions
MVP
MVP

Maybe these approaches give you some alternative ideas:

Replace(Replace(Range,'$',''),'&','-') as Range_num

Pick(Match(Ratio,'20% & Under','22.5%','25.0%'),   // add your other cases in analog way

'0-20',

'20.1-22.5',

'22.6-25.0'

) as Range_ration 

Or use a MAPPING table approach:

Data Cleansing

View solution in original post

4 Replies
MVP
MVP

Maybe these approaches give you some alternative ideas:

Replace(Replace(Range,'$',''),'&','-') as Range_num

Pick(Match(Ratio,'20% & Under','22.5%','25.0%'),   // add your other cases in analog way

'0-20',

'20.1-22.5',

'22.6-25.0'

) as Range_ration 

Or use a MAPPING table approach:

Data Cleansing

View solution in original post

Are you aware of internal document match. Hope that help you

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator III
Creator III

Thank you Stefan for your help. It worked for the 1st part,

I have replaced the 2nd nested if conditions with the following code, however it is only picking the '20% & Under' value and for rest it is showing null . I am not sure if i am making any syntax error.

Pick(Match(Ratio,'20% & Under','22.5%','25.0%','27.0%','29.0%','31.0%','33.0%','35.0%','39.0%','41.0%','43.0%','45.0%','47.0%','50.0%'),

'0-20',

'20.1-22.5',

'22.6-25.0',

'25.1-27.0',

'27.1-29.0',

'29.1-31.0',

'31.1-33.0',

'33.1-35.0',

'35.1-39.0',

'39.1-41.0',

'41.1-43.0','43.1-45.0','45.1-47.0','47.1-50.0'

) as Range_ration

Resident Table1;

MVP
MVP

Seems to work just fine. Probably your input values don't match exactely. What is the source of your data?

Could you post some sample records (excel file, QVD, etc)?

LOAD *,

Pick(Match(Ratio,'20% & Under','22.5%','25.0%','27.0%','29.0%','31.0%','33.0%','35.0%','39.0%','41.0%','43.0%','45.0%','47.0%','50.0%'),

'0-20',

'20.1-22.5',

'22.6-25.0',

'25.1-27.0',

'27.1-29.0',

'29.1-31.0',

'31.1-33.0',

'33.1-35.0',

'35.1-39.0',

'39.1-41.0',

'41.1-43.0','43.1-45.0','45.1-47.0','47.1-50.0'

) as Range_ration

INLINE [

Ratio

'20% & Under'

'22.5%'

'25.0%'

'27.0%'

'29.0%'

'31.0%'

'33.0%'

'35.0%'

'39.0%'

'41.0%'

'43.0%'

'45.0%'

'47.0%'

'50.0%'

];