Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Use of thousand and decimal separators in Qlikview

How do others handle separators in Qlikview when you have regions/countries using opposite standards.  If we choose one standard over the other we'll have issues when the region/country with the non-standard designation tries to export data into Excel.

Option 1

1,000.00

Option 2

1.000,00

One option would be to use period for the decimal separator and leave thousands blank, but this isn't an option as the thousands field needs to contain something.  A space for thousands is also not ideal as it isn't recognized in Excel.

Any ideas?

Thanks

1 Solution

Accepted Solutions

Re: Use of thousand and decimal separators in Qlikview

Hi Jonathan,

I don't know, this one useful or not for you.. but check it.

Load your separator data using inline with Country / Region. Then you can use the separator value in your expression with 'Num' function. for e.g.

QUALIFY *;
  Separator:
LOAD * INLINE [
Separator,  Integer,                        Decimal
Brazil,         "'#.##0', ',' , '.'",           "'#.##0,00', ',' , '.'"
US,             "'#,##0', '.' , ','",           "'#,##0.00', '.' , ','"
]
;

UNQUALIFY *;
  Sales:
LOAD 'Customer '&Ceil(Rand()*100) as Customer,
 
Rand()*1000 as Sales AutoGenerate 100;



Create the List box for Separator. (Always one selected value would be fine)


In the expression, you can just use like


=Num(sum(Sales),$(=Separator.Decimal))

Sample attached.


9 Replies
effinty2112
Honored Contributor

Re: Use of thousand and decimal separators in Qlikview

Hi Jonathan,

                         This is more tricky than it might seem. In the number format codes you can't select the characters to use as the thousand and decimal separators.

This expression is a bit awkward and I'm hopeful someone could come up something a bit simpler

=Replace(

Replace(

Replace(Text(num(1000,'#,##0.00')),'.','|'),

',','.'),

'|',',')

gives the text : 1.000,00

Good luck

Andrew

Not applicable

Re: Use of thousand and decimal separators in Qlikview

Maybe I should be clearer in my question.

Is there a way to represent both standards in Qlikview depending on the user or location?  Currently Qlikview is setup for only 1 standard.  Could Qlikview determine your location and adjust your setting or could the user select the format?

So someone from the US selects the format 1,000.00

And someone from Brazil selects the format 1.000,00

If this is solved in Qlikview then I think the extract to Excel would be solved.

A workaround might be like you suggest some type of extract script but this doesn't help non-standard users when using Qlikview.

MVP & Luminary
MVP & Luminary

Re: Use of thousand and decimal separators in Qlikview

Hi Jonathan,

Have you tested this? When I export to Excel it seems to apply my regional settings.

-Rob

Not applicable

Re: Use of thousand and decimal separators in Qlikview

Not yet.  I'll forward to our developer and have him test.  Thanks.

Re: Use of thousand and decimal separators in Qlikview

Hi Jonathan,

I don't know, this one useful or not for you.. but check it.

Load your separator data using inline with Country / Region. Then you can use the separator value in your expression with 'Num' function. for e.g.

QUALIFY *;
  Separator:
LOAD * INLINE [
Separator,  Integer,                        Decimal
Brazil,         "'#.##0', ',' , '.'",           "'#.##0,00', ',' , '.'"
US,             "'#,##0', '.' , ','",           "'#,##0.00', '.' , ','"
]
;

UNQUALIFY *;
  Sales:
LOAD 'Customer '&Ceil(Rand()*100) as Customer,
 
Rand()*1000 as Sales AutoGenerate 100;



Create the List box for Separator. (Always one selected value would be fine)


In the expression, you can just use like


=Num(sum(Sales),$(=Separator.Decimal))

Sample attached.


effinty2112
Honored Contributor

Re: Use of thousand and decimal separators in Qlikview

Hi Settu,

That's terrific. The QV help topic "Format Codes for Interpretation and Formatting Functions" says "It is not possible to set the separator in the format code.", but that's exactly what you've done!

Regards

Andrew

Not applicable

Re: Use of thousand and decimal separators in Qlikview

Works, the format selected is based on a users region domain.

Thanks for the help

 

 

Not applicable

Re: Use of thousand and decimal separators in Qlikview

The numbers exported are formatted as text, any fix for this?  Thanks Jon

image.png

Re: Use of thousand and decimal separators in Qlikview

Hi Jonathan,

I tried with my attached sample, it gives the proper number format for both region selected.