Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kvr9
Creator
Creator

Remove extra commas in preceding and end of concatenated field

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

 

 

 

1 Solution

Accepted Solutions
jyothish8807
Master II
Master II

Hi @kvr9 ,

Try this once:  🙂

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

 

 

Best Regards,
KC

View solution in original post

3 Replies
jyothish8807
Master II
Master II

Hi @kvr9 ,

Try this once:  🙂

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

 

 

Best Regards,
KC
kvr9
Creator
Creator
Author

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂