Use of thousand and decimal separators in Qlikview

[#1]

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

[effinty2112]

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

[#3]

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.

[rwunderlich]

Hi Jonathan,

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

-Rob

[#5]

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

[settu_periasamy · Accepted solution]

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]

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

[#8]

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

Thanks for the help

[#9]

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

[settu_periasamy]

Hi Jonathan,

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