topic Re: Nested IF in Load statement in QlikView

Nested IF in Load statement

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

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.

Re: Nested IF in Load statement

sunny_talwar — Thu, 31 Aug 2017 12:43:29 GMT

Try this

Table:

LOAD RowNo() as RowNum,

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

FROM

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

(ooxml, embedded labels, table is Sheet1);

Re: Nested IF in Load statement

ankur_abhishek — Thu, 31 Aug 2017 12:46:20 GMT

HI Bharani,

You can use below expression

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

Thanks and Regards

Ankur

Re: Nested IF in Load statement

bharani8 — Thu, 31 Aug 2017 12:48:53 GMT

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

Re: Nested IF in Load statement

ankur_abhishek — Thu, 31 Aug 2017 12:57:48 GMT

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

Re: Nested IF in Load statement

sunny_talwar — Thu, 31 Aug 2017 13:04:42 GMT

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

Re: Nested IF in Load statement

bharani8 — Thu, 31 Aug 2017 13:07:26 GMT

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

Re: Nested IF in Load statement

bharani8 — Thu, 31 Aug 2017 13:07:46 GMT

Thank you Sunny!! Got it!