topic Re: Comparing these two numbers in QlikView

Comparing these two numbers

Thu, 23 Jun 2016 14:02:41 GMT

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

swuehl — Thu, 23 Jun 2016 14:07:26 GMT

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

marcus_malinow — Thu, 23 Jun 2016 14:08:34 GMT

you could try rounding

so, for example

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

Re: Comparing these two numbers

florentina_doga — Thu, 23 Jun 2016 14:09:47 GMT

use num(field) in script load

Re: Comparing these two numbers

Digvijay_Singh — Thu, 23 Jun 2016 14:10:09 GMT

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

sorrakis01 — Thu, 23 Jun 2016 14:10:27 GMT

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

Anonymous — Thu, 23 Jun 2016 14:21:11 GMT

TABLE1:

LOAD *,

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

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

INLINE

[

A,B

000000000000865.55,865.55

]

;

LOAD

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

Resident TABLE1;

Re: Comparing these two numbers

MarcoWedel — Fri, 24 Jun 2016 23:27:56 GMT

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:

LOAD RecNo() as ID,

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

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