How to reduce multiple if condition in script editor

[ashis]

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

[swuehl · Accepted solution]

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

[Anil_Babu_Samineni]

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

[ashis]

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;

[swuehl]

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

];