Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
So maybe multiply by 100 and round your values.
you could try rounding
so, for example
round([number], 0.01) to round to 1/100s
use num(field) in script load
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
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
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;
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
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