topic Re: If condition issue in QlikView

If condition issue

jelly_gvm — Mon, 17 Aug 2015 14:59:30 GMT

Hi All,

below is my issue highlighted in red.

LOAD MESSAGE_CODE

MESSAGE_DESC,

if(Len(MESSAGE_CODE)>0, MESSAGE_DESC,MESSAGE_CODE)) as Details

FROM $(path)MESSAGE_CODES.qvd;

if the MESSAGE_CODE is exists then display MESSAGE_DESC dimension values else display MESSAGE_CODE dimension values, but it is displaying always MESSAGE_CODE dimension values in the report.

Can any one suggest me.

Thanks in advance.

Regards,

Ram

Re: If condition issue

sunny_talwar — Mon, 17 Aug 2015 15:06:02 GMT

Try this may be:

LOAD MESSAGE_CODE

MESSAGE_DESC,

if(Len(Trim(MESSAGE_CODE))>0, MESSAGE_DESC,MESSAGE_CODE)) as Details

FROM $(path)MESSAGE_CODES.qvd;

Re: If condition issue

sunny_talwar — Mon, 17 Aug 2015 15:07:16 GMT

Having given a recommendation, it just doesn't make sense that when MESSAGE_CODE is not available then you want to use it for details? Are you sure you don't want this?

LOAD MESSAGE_CODE

MESSAGE_DESC,

if(Len(Trim(MESSAGE_CODE))>0, MESSAGE_CODE,MESSAGE_DESC)) as Details

FROM $(path)MESSAGE_CODES.qvd;

Re: If condition issue

Mon, 17 Aug 2015 15:12:18 GMT

Hi,

As suggested by Sunny, you can try using Trim functions as your Condition.

Also, worth checking how are your values in 'MESSAGE_CODE' coming in QV and if you are getting '0' ?

It could sometimes be an issue with the Flag/ Binary numbers,

Try using Exists () function in your Condition.

Hope it helps !

Cheers !

Re: If condition issue

jelly_gvm — Mon, 17 Aug 2015 15:12:55 GMT

Thanks for your reply,

Nope, when ever the MESSAGE_CODE is exiting in same field then populate the MESSAGE_DESC values else populate MESSAGE_CODE and we should not use any hard-code values to this fields.

Thanks & Regards,

Ram

Re: If condition issue

jelly_gvm — Mon, 17 Aug 2015 15:16:23 GMT

it's not working my requirement could please change the IF condition.

Re: If condition issue

sunny_talwar — Mon, 17 Aug 2015 15:17:55 GMT

May be this:

LOAD MESSAGE_CODE

MESSAGE_DESC,

if(Len(Trim(MESSAGE_DESC))>0, MESSAGE_DESC,MESSAGE_CODE)) as Details

FROM $(path)MESSAGE_CODES.qvd;

Re: If condition issue

Mon, 17 Aug 2015 15:23:40 GMT

According to me, there is no sense in the expression you are using because you check against null on the MESSAGE_CODE and again give the null value if condition is false. Try to use below cond:

IF(LEN(TRIM(MESSAGE_DESC)),MESSAGE_DESC,MESSAGE_CODE)

Re: If condition issue

jagan — Mon, 17 Aug 2015 15:23:59 GMT

Hi,

This should definitely work, check again

TableName:

LOAD MESSAGE_CODE

MESSAGE_DESC,

If(Len(Trim(MESSAGE_DESC))>0, MESSAGE_DESC,MESSAGE_CODE)) as Details

FROM $(path)MESSAGE_CODES.qvd;

Regards,

Jagan.

Re: If condition issue

Anonymous — Mon, 17 Aug 2015 15:29:21 GMT

Hi Ram,

Try below code:

if(Len(Trim(MESSAGE_CODE))>0, MESSAGE_DESC,MESSAGE_CODE)) as Details

Regards

Neetha

Re: If condition issue

Oleg_Troyansky — Mon, 17 Aug 2015 15:30:13 GMT

I see two problems - one in the question, and one in the suggested answers...

1. The way your IF statement is formulated, you want to use the Code when the Code value doesn't exist - so, every time the code value is NULL, you want to see NULL in your field. It sounds "upside-down" to me. Usually we ask "if this value is NOT NULL, then use it, otherwise use another value".

2. In the suggested solutions, people keep suggesting to use TRIM(LEN(...)), which is meaningless. The function len returns the Length of the field, which is a number. The function TRIM is only useful when it's applied to the field itself, not to its length. So, if anything, the following can be considered:

LEN(TRIM(Field))

This combination of the two functions will first trim the spaces on both ends of the field, and then calculate the length of the remaining value. The meaning is to treat any number of spaces the same way as empty strings.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: If condition issue

jelly_gvm — Mon, 17 Aug 2015 15:32:27 GMT

Thank you all, it's working for me and issue with the data.

Re: If condition issue

Anonymous — Mon, 17 Aug 2015 15:34:04 GMT

HI Ram,

Does sunny's solution solves your issue?

Re: If condition issue

jelly_gvm — Mon, 17 Aug 2015 15:39:49 GMT

Thanks for your reply,

yes the Sunny's solution is working fine for me, if you have any other solution for this issue please share it.

Thanks &Regards

Ram

Re: If condition issue

sunny_talwar — Mon, 17 Aug 2015 15:48:18 GMT

Hi Oleg‌

Thanks for pointing out the incorrect order of the Len() and Trim() functions. I have fixed the order in the above posts, so that anyone seeing the thread in the future doesn't get confused. and I recommend Ram‌ to make note of the change as well.

Best,

Sunny