Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Problems with Thousand and Decimal Separator

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?

3 Replies
luis_pimentel
Contributor III

Re: Problems with Thousand and Decimal Separator

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.

Employee
Employee

Re: Problems with Thousand and Decimal Separator

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

Not applicable

Re: Problems with Thousand and Decimal Separator

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?

Community Browser