topic AW:Re: AW:Re: AW:Re: Remove last two digits not working in QlikView

Remove last two digits not working

Tue, 08 Feb 2011 14:21:43 GMT

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

Remove last two digits not working

Tue, 08 Feb 2011 14:29:02 GMT

Try this

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

Remove last two digits not working

Miguel_Angel_Baeyens — Tue, 08 Feb 2011 14:31:41 GMT

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.

AW:Re: Remove last two digits not working

Tue, 08 Feb 2011 14:37:40 GMT

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]

AW:Re: Remove last two digits not working

Miguel_Angel_Baeyens — Tue, 08 Feb 2011 14:47:35 GMT

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.

AW:Re: AW:Re: Remove last two digits not working

Tue, 08 Feb 2011 14:54:22 GMT

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

AW:Re: AW:Re: Remove last two digits not working

Miguel_Angel_Baeyens — Tue, 08 Feb 2011 15:07:08 GMT

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.

AW:Re: AW:Re: AW:Re: Remove last two digits not working

Tue, 08 Feb 2011 15:14:13 GMT

Thanks alot Miguel.

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

AW:Remove last two digits not working

Tue, 08 Feb 2011 15:46:34 GMT

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.