7 Replies Latest reply: Jun 24, 2016 7:27 PM by Marco Wedel

# Comparing these two numbers

I am using Qlikview to do some data validation between two systems. For the first system I am importing a csv file, and for the second I am bringing in data from an excel file. I am running into an issue when comparing a dollar amount field. The first system has leading zeroes in it's dollar amount field, it looks like this: 000000000000865.55, and the second system does not have the leading zeroes so it looks like: 865.55. Qlikview is telling me that these two values are not equal and I can't seem to get it to understand that they are. I have tried num() and ltrim() and they both do not work. Is there any way for me to do this? Thanks

• ###### Re: Comparing these two numbers

Are both values interpreted as numbers (e.g. right aligned in a default list box)?

You should take care when comparing floating values with decimals, have a look at

Rounding Errors

So maybe multiply by 100 and round your values.

• ###### Re: Comparing these two numbers

you could try rounding

so, for example

round([number], 0.01) to round to 1/100s

• ###### Re: Comparing these two numbers

Try -

=Round(Num(865.55,'#,##0.00'),.01)

When I manually compare - =if(000000000000865.55=865.55,1,0)

it works, could be rounding or type issue

• ###### Re: Comparing these two numbers

Hi,

swuehl said correctly this two fields aren't format number.

If you like you can insert Num function in the script... Num(Field1) as Num1, Num(Field2) as Num2

Regards

• ###### Re: Comparing these two numbers

TABLE1:

//SubStringCount(A,'0') AS COUNT,

RIGHT(A,(LEN(A)-(SubStringCount(A,'0')))) AS NEW_A

INLINE

[

A,B

000000000000865.55,865.55

]

;

IF(NEW_A=B,'MATCHED','NOTMATCHED') AS MATCHED_STATUS

Resident TABLE1;

• ###### Re: Comparing these two numbers

Hi,

at first I thought of using the Evaluate() function to interpret and load 000000000000865.55 as number (due to its length it will be loaded as text per default).

However this might cause some rounding issues:

```table1:
value1,
value2,
Evaluate(value1) as value3
Inline [
value1, value2
0000000000000000865.51, 865.51
000000000000000865.52, 865.52
00000000000000865.53, 865.53
0000000000000865.54, 865.54
000000000000865.55, 865.55
00000000000865.56, 865.56
0000000000865.57, 865.57
000000000865.58, 865.58
00000000865.59, 865.59
0000000865.60, 865.60
000000865.61, 865.61
00000865.62, 865.62
0000865.63, 865.63
000865.64, 865.64
00865.65, 865.65
0865.66, 865.66
865.67, 865.67
];
```

An alternative solution might be to purge the leading zeros like:

```Replace(Replace(LTrim(Replace(Replace(value1,' ','@SPACE@'),'0',' ')),' ','0'),'@SPACE@',' ') as value3
```

or

```Mid(value1,FindOneOf(value1,PurgeChar(value1,'0'))) as value3
```

or (keeping the format)

```Dual(value1,Mid(value1,FindOneOf(value1,PurgeChar(value1,'0')))) as value3
```

hope this helps

regards

Marco