Concatenating and Cleaning Empty Cells

Question

Table 1

JE00011000:Revenue  JE00021000:Revenue2000:Accounts Payable JE00031000:Revenue2000:Accounts Payable3000:Cash

Table 2

JE00011000:Revenue  JE00021000:Revenue, 2000: Accounts Payable  JE00031000: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 Answer

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,