topic Re: Replace last comma with dot in QlikView

Replace last comma with dot

mohan2391 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

i have a field data like below

1,11,111

22,222

3,33,333,3333

456,123,789,000,012

i want output like below

1,11.111

22.222

3,33,333.3333

456,123,789,000.012

I mean only the last comma (,) in the value should be replaced with dot(.)

If there are 3 commas in the field value, 1st 2 commas should be there but the 3rd comma should be replaced with dot

How to achieve this?

Re: Replace last comma with dot

olivierrobin — Thu, 12 Apr 2018 07:13:41 GMT

hello

you may try something like this :

let a='1,234,567';
let b=index(a,',',-1);
let a =left(a,b-1)&'.'& mid(a,b+1);
trace *** $(a) ***;

Re: Replace last comma with dot

mohan2391 — Thu, 12 Apr 2018 07:16:39 GMT

I'm loading data from excels. While loading itself i have to do this

Re: Replace last comma with dot

olivierrobin — Thu, 12 Apr 2018 07:19:53 GMT

you can put the formulas in the load

load left(left(a,index(a,',',-1)-1) & '.' & mid(a,index(a,',',-1)+1) as new_column

Re: Replace last comma with dot

d_pranskus — Thu, 12 Apr 2018 07:24:46 GMT

You can use the following snippet:

Suppose the column in Excel is Amount

LOAD

LEFT(Amount, INDEX(Amount, ',', -1) - 1) & '.' & MID(Amount, INDEX(Amount, ',', -1) + 1) AS Amount2

....

Re: Replace last comma with dot

mohan2391 — Thu, 12 Apr 2018 07:36:35 GMT

ThankYou!!

Re: Replace last comma with dot

olivierrobin — Thu, 12 Apr 2018 07:39:04 GMT

you' re welcome

remember to close the post

Re: Replace last comma with dot

mohan2391 — Thu, 12 Apr 2018 07:40:37 GMT

A small correction needed in this

no need of 2nd left()

By mistake it came i think

Re: Replace last comma with dot

olivierrobin — Thu, 12 Apr 2018 07:49:06 GMT

you're right

too quick for copy / Paste