Nested IF in Load statement

[bharani8]

Hi Experts - I have 2 columns A & B. I need to add an calculated field (Nested IF) in the load statement itself..

If value in column A is blank... i need the output as "No Value" else it has to go to the 2nd condition and check ... If value in Column B is Inactive.. it should give us inactive .. for values other than inactive.. it should give as Active.

Kindly find the attached excel..

I need this to be in the load statement itself.

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD RowNo() as RowNum,

A,

    B,

    If(Len(Trim(A)) = 0, 'No Value', If(B = 'Inactive', 'Inactive', 'Active')) as Value

FROM

[..\..\Downloads\load.xlsx]

(ooxml, embedded labels, table is Sheet1);

[ankur_abhishek]

HI Bharani,

You can use below expression

if(IsNull(A), 'No Value', if(B='Inactive', 'Inactive','Active'))

Thanks and Regards

Ankur

[bharani8]

Hey Sunny --Thank u very much..Got it Perfect.. Actually i tried like below..

  If(A = ' ', 'No Value', If(B = 'Inactive', 'Inactive', 'Active')) as Value

I was not getting it.. Can u plz tell me why? so that i can understand better..

Regards

Bharani

[ankur_abhishek]

Hi Bharani,

In the below expression you are basically searching for space (' ') in column A instead of Null values .

If(A = ' ', 'No Value', If(B = 'Inactive', 'Inactive', 'Active')) as Value

Null values, space (' '), 0 are different, you need to handle it separately.

Thanks and Regards

Ankur

[sunny_talwar]

Checking for space is not right because you mentioned that it is null... so Len(Trim(A)) checks if the the Len of Trimmed A is 0 or not... When it is 0, it means A is either null or made up of blank spaces

[bharani8]

Thank you Abhi!! That was a clear Explaination!!

[bharani8]

Thank you Sunny!! Got it!