topic Re: Expression in QlikView

Expression

Anonymous — Wed, 03 Feb 2016 16:56:44 GMT

What could be wrong with the following expression:

If(Len(Trim([Med Aid])) = 0, 'UNKNOWN', If([Med Aid] = 'PRIVATE', 'OUT-OF-POCKET',[Med Aid])) as [Payment Type]

Regards.

Chris

Re: Expression

sunny_talwar — Wed, 03 Feb 2016 16:58:47 GMT

Looks right to me. It isn't working?

Re: Expression

sunny_talwar — Wed, 03 Feb 2016 17:00:12 GMT

May be, just may be PRIVATE isn't upper case in your data? You can force it to be Upper and see if it resolves

If(Len(Trim([Med Aid])) = 0, 'UNKNOWN', If(Upper([Med Aid]) = 'PRIVATE', 'OUT-OF-POCKET',[Med Aid])) as [Payment Type]

Re: Expression

Gysbert_Wassenaar — Wed, 03 Feb 2016 17:00:44 GMT

The field name could be misspelled or have the wrong case. Same for the value 'PRIVATE'. Perhaps you need to check for if(UPPER([Med Aid]) = 'PRIVATE'

Re: Expression

uroboros — Wed, 03 Feb 2016 17:07:47 GMT

perhaps can be the upper case, you'll may use the function wildmatch, or in fact, upper, like Sunny and Gysbert recommended to you.

For performance, You should convert [Med Aid] with autonumber, and compare number is more efficient.

Re: Expression

HirisH_V7 — Wed, 03 Feb 2016 17:16:59 GMT

Hi,

Try Like this,

If(Len(Trim([Med Aid])) = 0, 'UNKNOWN', If(Trim([Med Aid]) = 'PRIVATE', 'OUT-OF-POCKET',[Med Aid])) as [Payment Type]

Check whether your Fields mentioned are exact ones or not as Suggested by Sunny.

HTH,

Hirish

Re: Expression

Anonymous — Wed, 03 Feb 2016 17:27:02 GMT

The database screen shoot is as follows:

It is in upper case

Re: Expression

Anonymous — Wed, 03 Feb 2016 17:28:27 GMT

Many thanks please see thread above

Re: Expression

Gysbert_Wassenaar — Wed, 03 Feb 2016 17:29:01 GMT

Any trailing spaces?

Re: Expression

sunny_talwar — Wed, 03 Feb 2016 17:30:36 GMT

Have you looked at Hirish V‌ suggestion? Trim([Med Aid]) before checking it for PRIVATE?

Re: Expression

Wed, 03 Feb 2016 17:45:24 GMT

Hi,

Perhaps, you are doing group by?????

Regards

Re: Expression

Anonymous — Wed, 03 Feb 2016 17:49:32 GMT

I am in the process of re-loading the application, will advise accordingly when through.

Regards

Re: Expression

Anonymous — Wed, 03 Feb 2016 17:50:31 GMT

Thanks Hirish, trying this out and will advise of the outcome

Re: Expression

Anonymous — Wed, 03 Feb 2016 18:20:41 GMT

Hi Hirish

Using the 'Trim' function does not change anything

Regards.

Re: Expression

Anonymous — Wed, 03 Feb 2016 18:29:54 GMT

Hi Roberto

Herewith the load script:

The initial expression with no inclusion of the second transformation, now commented out, was working perfectly.

Re: Expression

HirisH_V7 — Wed, 03 Feb 2016 18:30:48 GMT

You can See that or cant't Execute the part of the code !

Whats the issue your'e Getting exactly!

Re: Expression

dena_reavis — Wed, 03 Feb 2016 18:49:28 GMT

You may suspect there is a space in the field, which would be why you are using the trim function, but if that mystery space-looking character is not a space, trim will not work. I recently had an encounter with a non-breaking space that is unaffected by the trim function. Basically, your first condition must not be true. If this "Len(Trim([Med Aid]))" does not equal zero, the rest of your expression will not calculate as expected.

Consider restructuring your IF so you don't rely on that first. Try if Med Aid is Private or Out of Pocket, then Med Aid, else Unknown. Not certain code below is correct syntax.

If(Trim([Med Aid]) = 'PRIVATE', 'OUT-OF-POCKET',[Med Aid]),'UNKNOWN' ) as [Payment Type]

check if the first 7 characters are PRIVATE or 13 characters are OUT-OF-POCKET LIKE THIS:

if(mid([Med Aid],1,7)='PRIVATE','UNKNOWN',

if(mid([Med Aid],1,13)='OUT-OF-POCKET','UNKNOWN',...........

Re: Expression

sunny_talwar — Wed, 03 Feb 2016 19:09:39 GMT

Just an idea to explore, try this:

If(Len(Trim([Med Aid])) = 0, 'UNKNOWN', If(KeepChar([Med Aid], 'PRIVATE') = 'PRIVATE', 'OUT-OF-POCKET',[Med Aid])) as [Payment Type]

Re: Expression

sunny_talwar — Wed, 03 Feb 2016 19:18:01 GMT

Or even this:

If(Len(Trim([Med Aid])) = 0, 'UNKNOWN', If(WildMatch([Med Aid], '*PRIVATE*'), 'OUT-OF-POCKET',[Med Aid])) as [Payment Type]

Re: Expression

Anonymous — Wed, 03 Feb 2016 19:26:52 GMT

Thanks Sunny i and reloading and trying it out, will advise outcome