topic Re: Use of thousand and decimal separators in Qlikview in QlikView

Use of thousand and decimal separators in Qlikview

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Use of thousand and decimal separators in Qlikview

effinty2112 — Wed, 06 Jul 2016 20:39:07 GMT

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

Re: Use of thousand and decimal separators in Qlikview

Wed, 06 Jul 2016 20:53:33 GMT

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.

Re: Use of thousand and decimal separators in Qlikview

rwunderlich — Wed, 06 Jul 2016 21:30:36 GMT

Hi Jonathan,

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

-Rob

Re: Use of thousand and decimal separators in Qlikview

Wed, 06 Jul 2016 21:53:16 GMT

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

Re: Use of thousand and decimal separators in Qlikview

settu_periasamy — Wed, 06 Jul 2016 22:14:14 GMT

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.

Re: Use of thousand and decimal separators in Qlikview

effinty2112 — Wed, 06 Jul 2016 22:52:24 GMT

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

Re: Use of thousand and decimal separators in Qlikview

Thu, 07 Jul 2016 15:12:26 GMT

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

Thanks for the help

Re: Use of thousand and decimal separators in Qlikview

Tue, 12 Jul 2016 14:53:37 GMT

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

Re: Use of thousand and decimal separators in Qlikview

settu_periasamy — Tue, 12 Jul 2016 18:29:17 GMT

Hi Jonathan,

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