topic Re: How to reduce multiple if condition in script editor in QlikView

How to reduce multiple if condition in script editor

ashis — Wed, 25 Nov 2020 16:16:04 GMT

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

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

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

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

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

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

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

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

$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

Re: How to reduce multiple if condition in script editor

swuehl — Tue, 20 Sep 2016 17:45:43 GMT

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

Re: How to reduce multiple if condition in script editor

Anil_Babu_Samineni — Tue, 20 Sep 2016 18:04:03 GMT

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

Re: How to reduce multiple if condition in script editor

ashis — Thu, 22 Sep 2016 08:40:53 GMT

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;

Re: How to reduce multiple if condition in script editor

swuehl — Thu, 22 Sep 2016 17:27:40 GMT

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%'

];