Case When, including null values

[jacob_engelbrecht]

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

[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"

[jacob_engelbrecht]

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 · Accepted solution]

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_]

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]

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"