COMBINE FOUR FIELDS INTO ONE

[sadiaasghar]

I HAVE FOUR FIELDS. I WANT THEM TO COMBINE INTO ONE ROW BY ROW INTO A SINGLE COLUMN SEPARATED BY A COMMA. I AM DOING THIS IN DIMENSION 

[MilestoneOwner1]&','&[MilestoneOwner1]&','&[MilestoneOwner1]&','&[MilestoneOwner1] as [MilestoneOwner]

but it's giving me result like HSE,IT,, . What i want is if any field is null it should skip comma separator too like HSE,IT. I am attaching excel data for reference.

[sadiaasghar]

@Kushal_Chawda Please help me with this.

[Kushal_Chawda · Accepted solution]

@sadiaasghar  Try below.  Replace inline table and fieldnames according to your data

LOAD *,replace(trim(if(len(trim([Milestone Owner 1]))=0,'',[Milestone Owner 1]&' ')&
     if(len(trim([Milestone Owner 2]))=0,'',[Milestone Owner 2]&' ')&
     if(len(trim([Milestone Owner 3]))=0,'',[Milestone Owner 3]&' ')&
     if(len(trim([Milestone Owner 4]))=0,'',[Milestone Owner 4])),' ',',') as [Milestone Owner]
inline [     
Milestone Owner 1,Milestone Owner 2,Milestone Owner 3,Milestone Owner 4
A,,B,C
,B,C,
A,,,D
A,C,,
A,,,
,D,,
,,F,
,,,G ];

[sadiaasghar]

Thanks @Kushal_Chawda this is what i was looking for. Now if we want to concatenate text value of a alphanumeric field i.e. ProgressDescription /Achievement based on month and project in a single row.   I want to display it on current Month, Current year basis but the problem is some project has sub projects so they have multiple values for a single month i want them to concatenate by a line break.

[Kushal_Chawda]

I would advice to open new thread by marking correct answer as per original need as  this is totally new question. I would happy to help. Just want to make sure that the thread answer should not differ from what was actually asked as it will be difficult for other people to relate question and answer. I hope it make sense.