Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

7 Replies
swuehl
MVP
MVP

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.

marcus_malinow
Partner - Specialist III
Partner - Specialist III

you could try rounding

so, for example

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

florentina_doga
Partner - Creator III
Partner - Creator III

use num(field) in script load

Digvijay_Singh

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

sorrakis01
Specialist
Specialist

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

Anonymous
Not applicable
Author

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;

MarcoWedel

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

];

QlikCommunity_Thread_222243_Pic1.JPG

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

QlikCommunity_Thread_222243_Pic2.JPG

hope this helps

regards

Marco