Expression

[Anonymous]

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

[#2]

Hi,

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

Regards

[Anonymous]

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

Regards

[Anonymous]

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

[Anonymous]

Hi Hirish

Using the 'Trim' function does not change anything

Regards.

[Anonymous]

Hi Roberto

Herewith the load script:

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

[HirisH_V7]

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

Whats the issue your'e Getting exactly!

[dena_reavis]

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]

or

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',...........

[sunny_talwar]

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]

[sunny_talwar · Accepted solution]

Or even this:

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

[Anonymous]

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