Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Use of Len(Trim) with IsNull

I need to combine the Len(Trim) and IsNull functions as i believe they can bring different results depending on the data as follows:

  if(len(trim([Doctor Number])) = 0 or If(IsNull([Doctor Number])), '00000',  if(len(trim([Doctor Number])) >= 7 and [Retail Pharmacy]='PSMAS', '00001', [Doctor Number])) as [Doctor Number]


The above expression is not right, can anyone assist with the right one.


Regards

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Len(Trim()) will work for NULLs as well as blanks. So if you use Len(Trim()), you do not need IsNull() as well.

IsNull() works with NULLs but not blanks.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

8 Replies
PrashantSangle

Hi,

Try below

If (len(trim([Doctor Number]))=0,'00000',

     if(len(trim([Doctor Number]))>=7 and wildmatch([Retail Pharmacy],'PSMAS','00001',[Doctor Number]

     )

)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
tamilarasu
Champion
Champion

Hi Christopher,

Try,

  If(Len(Trim([Doctor Number])) = 0 or IsNull([Doctor Number]), '00000',

   If(Len(trim([Doctor Number])) >= 7 and [Retail Pharmacy]='PSMAS', '00001',

   [Doctor Number]) ) as [Doctor Number]

Edit: Check the below thread for better understanding.!! As mentioned by others, you can use Len(Trim(FieldName)) alone in this case.


IsNull(MyField)  vs. Len(MyField) = 0

Anonymous
Not applicable
Author

Hi Max

As mentioned in my post, it is not the second statement which is at issue. I am looking at using both IsNull and Len(Trim) to check for NULL or blanks

jonathandienst
Partner - Champion III
Partner - Champion III

Len(Trim()) will work for NULLs as well as blanks. So if you use Len(Trim()), you do not need IsNull() as well.

IsNull() works with NULLs but not blanks.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

I already mentioned here?

Expression consolidation

With Isnull() If not required as you are comparing field in same block of IF()...

its_anandrjs

Try this way

if(len(trim([Doctor Number])) = 0 or If(IsNull([Doctor Number]) = -1), '00000', if(len(trim([Doctor Number])) >= 7 and [Retail Pharmacy]='PSMAS', '00001', [Doctor Number]))as [Doctor Number]



Regards,

Anand

PrashantSangle

Hi,

as suggested Jonathan Len(Trim()) will work for Nulls as well as blanks and IsNull() works with nulls but not blanks.

So if you use one condition instead of two, It will work same.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Anonymous
Not applicable
Author

Many thanks, Tamil.  I am reloading the application and will check to see if my results are different.  I will advise accordingly.

Regards.