How to text wrap column data without data duplication under other columns?

[VinithaNair]

I have an excel table that needs to be emailed automatically to their respective email groups when the field is grouped by each "Sector". Ex: For Sector 2, the table data across only sector 2 needs to be sent to Email2@example.com.

Sector 1 data to Email1@example.com and Sector 3 data to Email3@example.com. I have attached a data file that goes as the input but the output from the "Basic Table" tool does not text wrap the "Holder" column as shown for "Company3" and "Company4" in the excel file. I want the table to look exactly like it does in the excel file, but when there are multiple holder names that need to be text wrapped under one cell, The  "Basic Tool" displays all the holder names in one row (extend the window pane under the "Browse" tool horizontally, and that is exactly how it gets displayed in the email) no matter how I configure the column/width settings in the Basic Table tool. When I split the "Holder" names into separate rows using "text to columns" tool, the data under the other fields get duplicated across "Holder4" and "Holder6". I want the data across the same ticker and company displayed only once and only the holder names text wrapped within a cell/ in separate rows for each company/Ticker.

Would be great if somebody could guide me through a workaround solution. Thanks! Cheers.

[binuacs]

@VinithaNair One option to edit the table PCXML using a formula tool,   attaching a sample workflow

[VinithaNair]

Thank you for the formula you provided! In terms of the "Holders" I think I provided straightforward dummy data which does not look anything like the actual data. Apologies there! I have updated the data file (attached) which has what the "Holder" names look like. Applying the Multi-row formula provided, does not work for the "Holder" names attached here. Would appreciate a workaround solution for this.

Cheers!

[binuacs · Accepted solution]

@VinithaNair let me know if the attached workflow works for you

[VinithaNair]

Thank you so much!! That formula actually helped with text wrapping! But the column header names need to remain as "1 Day Price Δ (%)" and "1 Week Price Δ (%)". The Delta sign changes in the output table. I tried connecting a select tool after the "Report Text" tool to rename the fields but they do not exist after passing it through the "Report Text" tool. Could you please help me fix this issue?

Highly appreciate the help so far :)

[binuacs]

@VinithaNair I don’t think those characters are supported by the report text tool, if you run the workflow, you will see the warning messages in the result window, I would suggest use some other ascii characters , may be ^ 

[VinithaNair]

Yes I can see the warnings under the "Report Text" tool but I am not sure if it is because the tool does not accept "Δ " symbol. If I run the old workflow without the formula tool, the "Δ " sign remains as it is in the output table when passed though "Report Text" tool (Attached files) even after I check the "validate PCXML" box. I am guessing the issue arises in the second formula tool we use and it maybe picking up another character before passing the the Report Text tool which is why we see the warnings? It is just a guess though, I am not sure. I applied another formula tool after the "Report Text" tool using:

REGEX_Replace([Table],"1 Day Price Δ (%)","1 Day Price Δ (%)")

REGEX_Replace([Table],"1 Week Price Δ (%)","1 Week Price Δ (%)")

But it does not work for the output table. Is there another formula that can be applied to get the Δ  sign back?

[binuacs]

@VinithaNair Which Alteryx version do you have? I ran this workflow in the latest version seems to be it is working

[VinithaNair]

I was working with the 2021.4.2.05617 version, but yes the whole workflow works without changing the data format for version 2023.1.1.123. However, when it comes to saving the email automation workflow on the Alteryx server, an error shows up for the latest version as opposed to the 2021 version.

So, I have just changed the "Δ" sign to an alternative text "change" and continued with the 2021 version. Thanks a lot for above solution though! That worked. Highly appreciate the effort :)

Cheers!

[binuacs]

@VinithaNair I was thinking of one option not sure it would work but give it a try, open your workflow created from the most recent version in a notepad and change the version number (will be in the top right corner) to the version which the server support and run.