Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I got a requirement to remove extra commas on concatenated field , below are few scenarios .
I have fields like city, district,state, Country need to derive a field Region as
City &','& district &','& state &','& Country as Region
some times the data it self consists ',' in the data and some times fields are missing resulting below situation
1.Agra,,UP,India
2.Madras,,Chennai,TN,
3. ,Delhi,,India
Kindly help me in solving the above situations
Hi @kvr9 ,
Try this once: 🙂
replace(if(Right(Region,1)=',',left(Region,len(Region)-1)),',,',',')
Hi @kvr9 ,
Try this once: 🙂
replace(if(Right(Region,1)=',',left(Region,len(Region)-1)),',,',',')
Thank you @jyothish8807 for your solution.
Sorry for late response.
The above solution is partially working, still i am getting comma on right side of the concated field
Request you to help me in resolving it
Thank you
check below script.
data:
Load *,
Replace(Replace(trim(Replace(Region,',',' ')),' ',','),',,',',') as new_Region;
Load city, district,state, Country,
city &','& district &','& state &','& Country as Region
Inline[
city, district,state, Country
Agra,,UP,India
Madras,,Chennai,TN,
,Delhi,,India
];
Regards,
Prashant Sangle