Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Marge the cells

Feras95p
8 - Asteroid

Dear all,

 

 

I have an excel file which contains the information of a restaurant orders, what I'm looking for is to group each order with a unique row to make it easier to be observed. Below I attached the picture of the input file which will be as input in the work flow:

 

Input.PNG   And here is the output file which I made it manually and I'm looking to be done by alteryx.

 

output.PNG

 

 

Any idea on how it can be done or any help in that please?

 

I also attached the excel files.

 

Thanks in advance for your kind support .

7 REPLIES 7
smoskowitz
12 - Quasar

You can do this with the Summarize tool. You can Group by, Sum by and Concatenate by. Give it a try and let me know if you need further assistance.

 

Thanks,

Seth

Feras95p
8 - Asteroid

Hey @smoskowitz,

 

  tried to do that but I don't have the choice of Concatenate by, I tried  by order number I grouped by and sum by but I don't have the Concatenate by choice. Could you please give me more details on which fields I have to to do that?   

smoskowitz
12 - Quasar

Hi @Feras95p --

 

This should get you started.

 

Seth

DannyS
Alteryx Alumni (Retired)

Hi @Feras95p 

 

In order to view the option to Concatenate by a certain field, you will need to ensure that that field is a string data type. If so, your configuration of the summarize tool will look something like this:Capture.png

Feras95p
8 - Asteroid

Hi @DannyS , unfortunately it doesn't give me the desired output as shown n the pictures attached below:-

 

 

1.PNG2.PNG 

DannyS
Alteryx Alumni (Retired)

HI @Feras95p 

 

I would go ahead and look at the example workflow attached above ^^^ by @smoskowitz to get you on the right track. This example workflow will not mimic the exact "merge cells" format like in Excel, but you can concatenate the records several ways. The example from @smoskowitz shows the "," separator but her is a list of things you can do to customize the concatenation process:

 

 

  • Start: The character specified will appear at the beginning of the concatenated string. It is left blank by default.
  • Separator: The character specified will appear in between each value of the concatenated string. The default is a comma ,.
  • End: The character specified will appear at the end of the concatenated string. It is left blank by default.

 

 

The start, separator and end elements must be specified for each field an action is being applied to. You can enter any character or string, or leave them blank. Supported escape characters include: \n (new line), \t (tab), \r (carriage return), and \s (white space character).

 

You can also rename the columns to your liking within the Summarize tool under the "Output Field Name" column for consistency.

 

One other thing I would mention is that it looks like some record values within your columns may contain misspellings or slight variations (e.g. Orajah vs Orijah and South 1 vs South1). I would suggest either using the replace function with the Formula tool or looking into the Fuzzy Match tool to clean up those slight variations. This link will give you an example of configuring the tool: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Fuzzy-Match/ta-p/45485

 

I've attached a workflow similar to the above, but with that added formula tool and different separator option.

 

DannyS
Alteryx Alumni (Retired)

*UPDATE*

 

@Feras95p ,

 

There is actually a similar discussion thread related to merging cells: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Merging-cells-with-the-Table-Tool/t...

 

The above article will show you how you can use the Table tool within the Reporting palette to merge cells, similar to Excel. Hope this is helpful!

Labels