if Statement

[priyarane]

Hi Expets,

How to write the below expression in our Script part.

First(IIf([Template]="Retail",[BaselII_Category] & " Pooled",[Cust_Name]))
If Cust Name is Blank,Null then Use Cust ID. If Cust ID is also blannk use Basel Category

Thank you

[priyarane]

Right now we can leave about First.

Can I write expression like this:

if

(Template = 'Retail' ,BaselII_Category &'-'& 'Pooled',
if(len(trim(Cust_Name))=0 or IsNull(Cust_Name) = -1,CIF_Cust_ID,if(len(trim(CIF_Cust_ID))=0 or IsNull(CIF_Cust_ID) = -1,BaselII_Category))) as Cust_Name,

[priyarane]

Hi Gysbert,

Right now we can leave about First.

Can I write expression like this:

if

(Template = 'Retail' ,BaselII_Category &'-'& 'Pooled',
if(len(trim(Cust_Name))=0 or IsNull(Cust_Name) = -1,CIF_Cust_ID,if(len(trim(CIF_Cust_ID))=0 or IsNull(CIF_Cust_ID) = -1,BaselII_Category))) as Cust_Name,

[priyarane]

Hi Peter, Can I go like below:

if(Template = 'Retail' ,BaselII_Category &'-'& 'Pooled',
if(len(trim(Cust_Name))=0 or IsNull(Cust_Name) = -1,CIF_Cust_ID,if(len(trim(CIF_Cust_ID))=0 or IsNull(CIF_Cust_ID) = -1,BaselII_Category))) as Cust_Name,

[Gysbert_Wassenaar · Accepted solution]

The isnull() function isn't necessary. Len(trim(...)) is also 0 for null values.

if (Template = 'Retail' ,BaselII_Category &'-'& 'Pooled',

     if(len(trim(Cust_Name))=0,

          if(len(trim(CIF_Cust_ID))=0, BaselII_Category, CIF_Cust_ID),Cust_Name)) as Cust_Name,

[Peter_Cammaert]

Yes, you can but IMHO it will not produce the results you expect. For example, if [Cust_Name] contains data, it may end up being changed into a NULL value. Two points:

1. Drop every IsNull() = -1 logical expression. The trick with len(trim()) handles NULL values too.
2. Consider this pseudo-code representation of your entire IF expression. I added a default NULL-value. Is this correct?
   IF Template = Retail
   THEN Cust_Name = BaselII_Category & '-' & 'Pooled'
   ELSE
     IF Cust_Name is NULL or empty string
     THEN Cust_Name = CIF_Cust_ID
     ELSE
       IF CIF_Cust_ID is NULL or empty string
       THEN Cust_Name = BaselII_Category
       ELSE Cust_Name = NULL

Best,

Peter