Qlikview read a field as null when there is actually a value inside.

[Anonymous]

Hi Guys,

I have this issue where i use this formulae to create a field tagging whether the field is null or not. However, when the file has been loaded complete, there are some cases where it reads the field as null when is it not. I also saw a pattern that those cases that it has been reading wrong are values instead text inside the data.

if(IsNull(A_POLICY_NO),0,1) AS ISSUEDQUOTE

What could be wrong?

See image 1 - When null issuedquote is 0

See image 2- Table box show issuedquote with value 0 but with A_POLICY_NO (not null)

There are patterns that show those cases involve item with numbers only and not text policyno as per below. However there are cases with numbers that have no issues.

However if i tried to use set analysis to define this cases , qlikview able to pick it up correctly.

[its_anandrjs]

In your field change ISSUEDQUOTE to

if(IsNull(A_POLICY_NO) = -1 OR LEN(A_POLICY_NO) = 0,1,0) AS ISSUEDQUOTE

[shanky1907]

Better to use it like:

if(LEN(A_POLICY_NO) = 0,0,

if(IsNull(A_POLICY_NO) = -1,0,1) AS ISSUEDQUOTE

[Anonymous]

I have tried both, same result. there are still cases read as null when its not.

[Anonymous]

I wish i could past you guys the data, but its 1gb app and growing

[shanky1907]

Did you use ALT fucntion?

[Anonymous]

I did some read on that but didnt go deeper. Care to elaborate how?

[Colin-Albert]

Alt applies to numeric data only not to text.

Try just testing the field length instead   LEN(TRIM(A_POLICY_NO))  and not using IsNull()

[Anonymous]

Hi All,

After further investigation it seems there are loading error when using formula in script. however formulae in expression does work.

I have one field which have the same string value of 0000000032217

Field                                                     Formula

P_MOTOR_BASICPREM_COMM1 = 0000000032217 ( Original Data)

P_MOTOR_BASICPREM_COMM   = Ltrim(Replace(P_MOTOR_BASICPREM_COMM),' '))

P_MOTOR_BASICPREM_COMM2 = NUM(EVALUATE(P_MOTOR_BASICPREM_COMM),'#0')

as you can see, the 2nd row shows the correct value, however the 1st row somehow change to unknown number when it applies the same formula.

However when I use the same formula at expression below, it appear correctly for both (List Box).

Expression formula  = Replace(P_MOTOR_BASICPREM_COMM),' '))     

Have you guys ever found the same issue? loading script bug?

[Anonymous]

sorry here is the image