QLIKVIEW - IF STATEMENT IGNORING SOME VALUES BUT NOT OTHERS (FIELDS LOAD OK)

[Embers_P_21]

Hello Qlik Community,

I have the below set of statements to calculate emissions for Air, Road and Ocean. Values from all fields load fine but out of some 7000 shipments for the Road Emission calculation I'm getting no results for about 40 of them where there are values in the source fields. The same calculation works with no issues for all other modes.

The source report is .xls and values are formatted as text. The only time the calculation worked for all shipments was when I multiplied all values by 1. Changing the format to either General or Number didn't work either. I tried to apply '=IF(ISBLANK(CJ2),"",NUMBERVALUE(CJ2,".",","))' to the fields affected in the source report with no success.

I'm at my wits end as to why QV only has an issue with such a small fraction of text-formatted numbers and would appreciate any suggestions...

THANKS A LOT!

IF (CO2_TRANS_MODE_LEG_1 = 'Motor (Common Carrier)', CO2_EMISSION_LEG_1,0) +
IF (CO2_TRANS_MODE_LEG_2 = 'Motor (Common Carrier)', CO2_EMISSION_LEG_2,0) +
IF (CO2_TRANS_MODE_LEG_3 = 'Motor (Common Carrier)', CO2_EMISSION_LEG_3,0) +
IF (CO2_TRANS_MODE_LEG_4 = 'Motor (Common Carrier)', CO2_EMISSION_LEG_4,0) +
IF (CO2_TRANS_MODE_LEG_5 = 'Motor (Common Carrier)', CO2_EMISSION_LEG_5,0) +
IF (CO2_TRANS_MODE_LEG_6 = 'Motor (Common Carrier)', CO2_EMISSION_LEG_6,0) +
IF (CO2_TRANS_MODE_LEG_7 = 'Motor (Common Carrier)', CO2_EMISSION_LEG_7,0) +
IF (CO2_TRANS_MODE_LEG_8 = 'Motor (Common Carrier)', CO2_EMISSION_LEG_8,0) +
IF (CO2_TRANS_MODE_LEG_9 = 'Motor (Common Carrier)', CO2_EMISSION_LEG_9,0) +
IF (CO2_TRANS_MODE_LEG_10 = 'Motor (Common Carrier)', CO2_EMISSION_LEG_10,0) AS CO2_ROAD_EMISSIONS_KGS

[Or]

I'd suggest checking the problem rows to see if at least one of the ten CO2_EMISSION_LEG_ fields contain a non-numeric value (or an actual null value), in which case the entire expression will evaluate to null as you can't add non-numeric values to numeric values using addition. If this is the case (and in general), I'd suggest using RangeSum() over your if() statements instead of simple addition, as this will prevent null/non-numeric values from breaking the entire thing.

[Embers_P_21]

Thanks a lot!! 🙂 I'm very very new to Qlikview - let me try the RangeSum()

[Embers_P_21]

Hi there, not sure if I'm overthinking the RangeSum() but I can't seem to find a way to sum the values where CO2_EMISSION_LEG_ 1-10 = Air/Road/Ocean for individual shipments in a new column?

Would you be able to point me in the right direction please? Thanks!!

SO Number	Trans Mode Leg 1	Distance (Leg 1)	Emission (Leg 1)	Trans Mode Leg 2	Distance (Leg 2)	Emission (Leg 2)
7805053	Motor (Common Carrier)	36.86	6.14
7805070	Motor (Common Carrier)	47.86	2.68	Air	9,326.07	364.21

[Or · Accepted solution]

The use of RangeSum() here simply replaces the regular addition (+). Instead of using If(1) + If(2) + ... + If(10), you would use RangeSum(If(1),If(2)...). Unlike regular addition, RangeSum won't return null even if some of the values are non-numeric, as those values will just be treated as zero. I'm not sure if that's the issue you're dealing with, as there's no way to know for sure without access to the actual problem data, but it's the first thing I'd try.

[Embers_P_21]

works perfect, thank you!