topic Re: comma instead of dot in QlikView

comma instead of dot

Anonymous — Sun, 29 Mar 2015 14:17:24 GMT

In my all reports on on Access Point, I am having problem by getting commas instead of dot.

Example: 123,45,88 instead of 123,45.88 (I also need Thousand seperator)

Reports work fine on my local machine.

Any changes need to apply on Access point side ?

Thanks

Re: comma instead of dot

Anonymous — Sun, 29 Mar 2015 14:27:44 GMT

In addition to sunindia - you can specify your default settings:

SET ThousandSep=',';

SET DecimalSep='.';

Re: comma instead of dot

sunny_talwar — Sun, 29 Mar 2015 14:29:11 GMT

Hahahaha msolomov I had to delete my response because I was not sure if what I said would work or not. .

Best,

Sunny

Re: comma instead of dot

Anonymous — Sun, 29 Mar 2015 14:30:10 GMT

Anyway, I'm not changing my post

Re: comma instead of dot

sunny_talwar — Sun, 29 Mar 2015 14:32:18 GMT

I feel what you have specified in itself would fix his problem. So my response would just have been redundant even if it was to work.

Best,

Sunny

Re: comma instead of dot

Anonymous — Sun, 29 Mar 2015 14:33:18 GMT

I have these only

Thanks

Re: comma instead of dot

Anonymous — Sun, 29 Mar 2015 14:34:29 GMT

Hi Sunny

I appreciate your time on Sunday morning.

For decimal I have variable vDecimal with values 0, 0.0, 0.00, 0.000, 0.0000, 0.00000), so because of this I cant LOAD values as you suggested here

Num((Sum({$<SCENARIO_ID = , QUARTER = {99}>} ATTRIBUTE_VALUE)/1000000),vDec)
And as I said EXPORT work fine on local PC but not on Access Point

What can be done?

Thanks

Re: comma instead of dot

Anonymous — Sun, 29 Mar 2015 14:49:37 GMT

I see you have some strange format of the data. Assuming it was a typo, and you mean 123,456,88, this will work:

=num(left('123,456,88', index('123,456,88',',',-1)-1) + subfield('123,456,88',',',-1)/100, '###,###.00')

Re: comma instead of dot

Anonymous — Sun, 29 Mar 2015 19:31:48 GMT

Hi Michael

Data format also good, it is numbers only and it's work absolutely fine on local PC but not on Access Point.

I have millions of row and all populated with this format. In this decimal also variable. Something wrong at Access point side.

Re: comma instead of dot

Anonymous — Sun, 29 Mar 2015 19:40:32 GMT

This is really strange... Maybe you have some unusual format settings on the QV Server box (?) It is in control Panel, Region and Language, Additional settings. There you have number formats.

If nothing helps, maybe you can upload an example that works tis way, I'll try on my server on Monday.

Re: comma instead of dot

Anonymous — Sun, 29 Mar 2015 20:12:16 GMT

Hi Michael

Thanks for quick reply.

I know this is some strange. There is no point to upload sample QV as it's work fine local PC. I will check the setting as you said on server side tomorrow. But anyway I will send sample tomorrow.

Thanks again for your help

Re: comma instead of dot

Anonymous — Fri, 03 Apr 2015 11:49:31 GMT

Hi Michael

Sorry for late reply.

I found the problem on server(comma instead of decimal point) as you suggested in this post.

I appreciate your suggestion

Thanks

Re: comma instead of dot

Fri, 03 Apr 2015 12:58:13 GMT

Hi,

You can use replace function.

Re: comma instead of dot

Anonymous — Mon, 06 Apr 2015 13:05:13 GMT

Hi Krishnama

Thanks for reply.

I have used comma for thousand seperation also.

Also my decimal place is also variable, user can select from 0 to 6 decimal (like 123,459 to 123,458.564823)

so hard code with replace I dont think possible.

I have used below macro code for export to excel, fyi

sub AdhocToExcel
'Set the path where the excel will be saved
filePath = "\\DBG.ADS.DB.COM\NYC-USERS-U\users106\parajen\config\Desktop\DB\Testz.xls"
'Create the Excel spreadsheet
Set excelFile = CreateObject("Excel.Application")
excelFile.Visible = true
'Create the WorkBook
Set curWorkBook = excelFile.WorkBooks.Add
'Create the Sheet
'first chart object
Set curSheet = curWorkBook.WorkSheets(1)
'loop
chartArray = Array("CHAD03", "CHAD04")
usedRows=0
For Each chart In chartArray
Set i = ActiveDocument.GetSheetObject(chart)
chartCaption = i.GetCaption.Name.v
curSheet.Cells(usedRows+1, 1)=chartCaption
i.CopyTableToClipboard true
' curSheet.Cells(usedRows+3, 1).Select '--> This line is same as below but skip one line between heading
curSheet.Cells(usedRows+2, 1).Select
curSheet.Paste
usedRows=curSheet.UsedRange.Rows.Count+3 '--->function to get the first unused row
Next

'loop end

excelFile.Visible = true

'Save the file and quit excel
' If we need to Save Excel file, Close & Quit Excel than remove comments for below 3 line, By commenting it will simly create & open excel
' curWorkBook.SaveAs filePath
' curWorkBook.Close
' excelFile.Quit

'Cleanup
Set curWorkBook = nothing
Set excelFile = nothing
end sub

Re: comma instead of dot

Mon, 06 Apr 2015 13:19:58 GMT

HI Satish,

try with this function

To solve it you may need to use the Num#() function to interpret it correctly,

e.g.

Num#( Field, '#', ',', '.') as InterpretedField

Re: comma instead of dot

Anonymous — Mon, 06 Apr 2015 17:32:05 GMT

Hi Avinash

It would not work because report placed on server residing Gemany, They have set up in Control panel(comma instead of decimal point) I need to overcome that set up. Works fine anywhere in USA.

Please let me know if you have any solution
I appreciate your suggestion, Thanks