topic Re: Adding Two fields with one of them is Null in QlikView

Adding Two fields with one of them is Null

sindhura — Tue, 23 Feb 2016 22:04:06 GMT

I have two fields Tax and Credit.

Credit = Tax + Original Price.

But for some of fields, I have Tax fields as Null.

so I should get Credit = Original Price.

but i see Null in the report for this field as well.

I have used the expression Tax + Original Price

Calculated dimension is also giving me the same result : Num (([Tax]+[Original Price]), '#,##0.00')

Can anyone please help me

Peter_Cammaert — Tue, 23 Feb 2016 22:18:31 GMT

Use RangeSum(Tax, [Original Price])

RangeSum will add individual values and treat NULL values as 0.

Best,

Peter

sunny_talwar — Tue, 23 Feb 2016 23:59:49 GMT

Although Peter's solution is the best possible, but just for a sake of an alternative you can also do this:

Alt(Tax, 0) + Alt([Original Price], 0)

jagan — Wed, 24 Feb 2016 01:04:24 GMT

Hi,

While loading your data in script try to handle this using Alt() or Len(Trim()) like below, handling this in Front end is not a good idea.

Data:

LOAD

Alt(Tax, 0) AS Tax_New

FROM DataSource;

Data:

LOAD

If(Len(Trim(Tax)) = 0, 0, Tax) AS Tax_New

FROM DataSource;

Now use Tax_New in the charts.

Hope this helps you.

Regards,

Jagan.

sindhura — Wed, 24 Feb 2016 19:33:31 GMT

All the scenarios worked.

Thanks a lot

jagan — Fri, 26 Feb 2016 01:30:11 GMT

Hi,

If you got the answer please close this thread by giving correct Answer to the post which helps you.

Regards,

Jagan.