Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mohan2391
Creator II
Creator II

Replace last comma with dot

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?

1 Solution

Accepted Solutions
olivierrobin
Specialist III
Specialist III

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

View solution in original post

8 Replies
olivierrobin
Specialist III
Specialist III

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) ***;

mohan2391
Creator II
Creator II
Author

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

olivierrobin
Specialist III
Specialist III

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

d_pranskus
Partner - Creator III
Partner - Creator III

Hi

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

     ....

mohan2391
Creator II
Creator II
Author

ThankYou!!

olivierrobin
Specialist III
Specialist III

you' re welcome

remember to close the post

mohan2391
Creator II
Creator II
Author

A small correction needed in this

no need of 2nd left()

By mistake it came i think

olivierrobin
Specialist III
Specialist III

you're right

too quick for copy / Paste