Remove extra commas in preceding and end of concatenated field

[kvr9]

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

[jyothish8807 · Accepted solution]

Hi @kvr9 ,

Try this once:  🙂

replace(if(Right(Region,1)=',',left(Region,len(Region)-1)),',,',',')

[kvr9]

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

[PrashantSangle]

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