topic Re: Qlikview read a field as null when there is actually a value inside. in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

its_anandrjs — Fri, 06 Oct 2017 08:40:35 GMT

In your field change ISSUEDQUOTE to

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

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

shanky1907 — Fri, 06 Oct 2017 08:47:44 GMT

Better to use it like:

if(LEN(A_POLICY_NO) = 0,0,

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

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

Anonymous — Fri, 06 Oct 2017 08:53:29 GMT

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

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

Anonymous — Fri, 06 Oct 2017 09:09:16 GMT

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

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

shanky1907 — Fri, 06 Oct 2017 09:22:13 GMT

Did you use ALT fucntion?

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

Anonymous — Fri, 06 Oct 2017 09:25:55 GMT

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

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

Colin-Albert — Fri, 06 Oct 2017 11:40:28 GMT

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()

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

Anonymous — Fri, 13 Oct 2017 03:53:37 GMT

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?

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

Anonymous — Fri, 13 Oct 2017 03:54:24 GMT

sorry here is the image