Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
jacob_engelbrecht
Contributor
Contributor

Case When, including null values

Good day 

I trust that someone with more knowledge will be able to solve this one for me.

I have a field that contains policy numbers that contains contains numeric as well as an alphanumeric character (Eg. 012345678x9) If the Policy number starts with 04 or 05 it is a new product series and all else is the older products.

The following load script does work to determine the  product type:

case when (Policy_NR like '04%' or Policy_NR like '05%') then 'New_Product' else 'Old_Product' end as "Product_Type"

My problem is that there is some blank fields in the data where no Policy number is provided and currently the "null" values is also seen as 'Old_Product' and I would like to see it separately.

Ideally my finished script should look like this:

012345678x9       Old_Product
041234567x6       New_Product
-                                 Unknown

Thanks in advance

Labels (2)
1 Solution

Accepted Solutions
Or
MVP
MVP

This doesn't look like Qlik Script, but rather SQL (from an unknown database type)...

That said, perhaps:

case when (Policy_NR like '04%' or Policy_NR like '05%') then 'New_Product'

when Policy_NR like '%' then 'Old_Product'

end as "Product_Type"

In Qlik Script, this would be:

If(WildMatch(Policy_NR,'04*','05*'),'New Product',if(len(Policy_NR)>0,'Old Product'))

View solution in original post

5 Replies
MayilVahanan

HI 

Try like below

case when (Policy_NR like '04%' or Policy_NR like '05%') then 'New_Product'

when  Len(RTRIM(LTrim(Policy_NR))) > 0 then 'Old_Product'

else 'UnKnown' end as "Product_Type"

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
jacob_engelbrecht
Contributor
Contributor
Author

Thank you for the reply. 

If I try it I get the following error "No authorized routine named "LEN" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884"

I am requesting this as part of a load statement so not sure if it makes a difference? Further how will the "null" values be converted to a value called "unknown"

Any other ideas ?

Or
MVP
MVP

This doesn't look like Qlik Script, but rather SQL (from an unknown database type)...

That said, perhaps:

case when (Policy_NR like '04%' or Policy_NR like '05%') then 'New_Product'

when Policy_NR like '%' then 'Old_Product'

end as "Product_Type"

In Qlik Script, this would be:

If(WildMatch(Policy_NR,'04*','05*'),'New Product',if(len(Policy_NR)>0,'Old Product'))

Iswarya_
Creator
Creator

Hi @jacob_engelbrecht ,

You can try the Qlik script as:

if(Wildmatch(Policy_NR,'04*','05*'),'New_Product',

   if(len(trim(Policy_NR))>0,'Old_Product','Unknown'))

jacob_engelbrecht
Contributor
Contributor
Author

 

I tried the Qlik script and got the following error

Connector reply error: SQL##f - SqlState: 42601, ErrorCode: 4294967192, ErrorMsg: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "else 'UnKnown' end" was found following "icy_NR)>0,'Old Product'))". Expected tokens may include: "<space>". SQLSTATE=42601

However, the following did work

case when (Policy_NR like '04%' or Policy_NR like '05%') then 'New_Product'

when Policy_NR like '%' then 'Old_Product'

end as "Product_Type"