Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
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
Highlighted
Partner
Partner

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.

Highlighted

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

Highlighted
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?