Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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) ***;
I'm loading data from excels. While loading itself i have to do this
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
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
....
ThankYou!!
you' re welcome
remember to close the post
A small correction needed in this
no need of 2nd left()
By mistake it came i think
you're right
too quick for copy / Paste