topic Re: Problems with Thousand and Decimal Separator in QlikView

Problems with Thousand and Decimal Separator

Mon, 17 Feb 2014 12:19:39 GMT

Hi,

we are using QlikView to load different Excel files or .txt files.

Some of them have a German Seperator Logic, like . for Thousands and , for Thousands and others are English.

So the seperaters are switched.

We tryed to solve this by using mulitple times the Script command:

SET ThousandSep='.';

SET DecimalSep=',';

or the other way around, each time before we load another file.

But this does not solve our problem. When we load the Englisch files we get no the right numbers in QlikView. Do you have an tips for me?

Re: Problems with Thousand and Decimal Separator

luis_pimentel — Mon, 17 Feb 2014 12:31:11 GMT

If you have source files in both formats, I only can think on replacing '.' and ',' (use the Replace() function ) when loading the files, so all of your resulting tables have the same formatting.

Re: Problems with Thousand and Decimal Separator

hic — Mon, 17 Feb 2014 12:53:57 GMT

You should use the Num#()-function to override the settings. It has four parameters: Field, format, decimal separator, thousand separator.

Num#(EnglishNumber,'#','.',',')

Num#(GermanNumber,'#',',','.')

HIC

Re: Problems with Thousand and Decimal Separator

Mon, 17 Feb 2014 16:09:35 GMT

Simple Num# does not work for me but

Num(Num#(EnglischNumber,'#,##0.0','.',','),'#.##0,0',',','.') as GermanNumer,

works. But I don't think it is the best solution?!

edit:

Num#(EnglishNumber,'#','.',',') as GermanNumber,

seems also to be working.

Is there a possibilty to change mutliple fields with one step?