Remove last two digits not working

[#1]

Dear Ladies and Gentlemen,

I'm importing data from excel which has the following format: 1111.111111

In the Script I trying to get rid of the dot and then cut the last 2 digits.

In the script this line does not work:

left(text(replace(Revenue,'.','')),(len(text(Revenue))-2)) AS [Net Revenues]

WHY?? It should be correct.

Doint the same line in a pivot works!

=left(text(replace([Net Revenues],'.','')),(len(text([Net Revenues]))-2))

THANK YOU FOR YOUR HELP!

// Using V9 SR6 32bit @ Win XP Service Pack 3, ~4GB Ram

[#2]

Try this

=left(purgechar([Net Revenues],'.'),len([Net Revenues]-3)

[Miguel_Angel_Baeyens]

Hello Daniel,

My guess is that although in the assistant you see those "dots" they maybe commas or any other character. So frst import raw data and see, say in a text object, if that replacement works. Check that the file looks fine in excel when you open it before importing (it if's getting a dump from any process, it may be some sort of conversion in that process that leaves different decimal or thousand separator than the one defined in your script variable ThousandSep or DecimalSep).

Hope this helps.

[#4]

Dear Miguel,

Dear shans_bala,

thank you for your comments. The dot in the number is ok and not according to any format/decimals.

Replacing the dot with nothing works: replace(Revenue,'.','')

The length of the whole string is always correct: (len(text(Revenue))-2))

But, when I'm trying to cut the last two digits with the 'LEFT' Function, it is not:

left(text(replace(Revenue,'.','')),(len(text(Revenue))-2)) AS [Net Revenues]

[Miguel_Angel_Baeyens]

Hi,

I may be missing something, but your syntax seems ok to me, and that's what makes me think of the data source. Since you are using Text() function when the value is a number, is there any chance that the Text(Revenue) is returning something else than the raw number? Try just loading the field as a string and check it

Text(Revenue) AS Revenue

Regards.

[#6]

It has the same effect as it imported as a number.

[Miguel_Angel_Baeyens]

Don't think it's a question of leading/trailing zeroes/blanks since length is OK. I've tried here with some dummy data form excel file and your expression in field conversion works fine (last two decimals are truncated).

Regards.

[#8]

Thanks alot Miguel.

This is what drives me crazy right now. Everything is fine, but it does not work at all.

[#9 · Accepted solution]

I could solve it:

The solution right here:

num(left(text(replace(Revenue,'.','')),(len(text(replace(Revenue,'.','')))-3))) AS [Net Revenues]

The Len-Function was not correct!

Thanks to all contributors.