IsNull() and Len(Trim) not working as expected on one field

[ArjunPrasad]

Hi All,

I trying to use IsNull() and len(trim) functions to display the other static values when they have Nulls

but it's not working as expected, 

expression in script:  isNull(AB_TRANS_NO),1,0) as AB_TRANS_NO

If(len(Trim(AB_TRANS_NO))=0 or isNull(AB_TRANS_NO),1,0) as AB_TRANS_NO,

it is giving only 0 value

not able to find the issue in it , plz find attached excel data 

can anyone suggest plz.

Thanks

Arjun

[Andrei_Cusnir · Accepted solution]

Hello,

I have a hypothesis that might explain why you are always getting 0 as response. I have taken a look at your dataset and I can see that "Null" values are actually represented with "-". IsNull() function will return 0 for "False" and -1 for "True". If you are loading the particular EXCEL file as it is, then Qlik Sense will check the character "-" and will return 0 which indicates that the value is NOT null. 

For example, I have opened the EXCEL file and cleared all the cells that container "-" characters. Now after reloading the data, I can see:

As you can see, the "NULL" fields are represented by "-", but only because Qlik Sense wants to tell you that this is NULL value and it is not actually characters within the dataset. Also the response now is 0 for the cells that have value and -1 for all the cells that doesn't have value.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 

[anat · Accepted solution]

try like :

if(AB_TRANS_NO='-',1,0)