Concatenating and Cleaning Empty Cells

[Inactive User]

Table 1

JE0001	1000:Revenue
JE0002	1000:Revenue	2000:Accounts Payable
JE0003	1000:Revenue	2000:Accounts Payable	3000:Cash

Table 2

JE0001	1000:Revenue
JE0002	1000:Revenue, 2000: Accounts Payable
JE0003	1000:Revenue, 2000: Accounts Payable, 3000: Cash

How can I get from table 1 to table 2? I tried using concatenate with ", " but it would give me values like "1000:Revenue, , ," Is there a way to perform the concatenate function and then remove the , , for entries that did not have a column 3 or 4? Is there another way to get where I want to go?

[Thableaus · Accepted solution]

Hi @Inactive User 

Here's a possible solution:

- Transpose your fields grouping by your first Field

- Add a "Field" label to your new concatenated field

- Use Data Cleansing Tools to replace possible whitespaces to blanks

- Use Filter tool to bring not empty records

- Cross-Tab tool to concatenate all values grouping by your first field again

WF attached

Cheers,

[JShankman]

Try the attached workflow? You can use the RegEx_Replace to get rid of the commas after everything is concatenated. The expression included gets rid of all the commas at the end of a string.