String truncation after Summarize-Concatinate

[dakeefe]

All,

I have two columns of string data, and am using the Summarize tool to first group by column A, then concatenate all of the strings in column B. However, Alteryx is truncating my data by default, and is only keeping the first 256 characters of the concatenation. I'll probably need as many as 4000 characters in a string, to then be passed through a join, append data, etc for analysis.

Is there a way to configure the Summarize tool to allow for longer strings after it concatenates?

[JohnJPS]

Hi @dakeefe

Just curious - what happens if you were to first run it through a Select tool, and specify a V_String (variable-length string) for Column B, and give it a very long string length.  Does it still truncate?

Hope that helps!

John

[dakeefe]

John:

Thank you for your suggestion. I'm using the V-WString data type since the strings contain "-" and adjusted the length to 5000, but I'm still getting truncated data:

[gc · Accepted solution]

Maybe a multirow formula tool, where you add contents of Row-1 to Row from Col B values in a new field, grouped by Col A?  So the final row of each Col A group has the full concatenation of Col B values, and you filter/include only those rows downstream? Sorry it's just a suggestion and not a solution, but I thought it might help.

[adm510]

Try using WString instead, I ran into the same issue with variable length string and this solved it. 

Note, per the documentation, WString is limited to 8192 characters (which meets your 4000 upper limit).

https://help.alteryx.com/9.5/Reference/DataFieldType.htm

I added a Field1-Start/Field1-End and Field2-Start/Field2-End tag to verify that all of field1 and field2 were concatenated intact which I verified by copying+pasting the result into another program. Alteryx doesn't accept images so this comment doesn't make any sense.

[JohnJPS]

HI @dakeefe,

I understand that the screenshot will truncate for the purposes of displaying on screen, but I believe the ellipsis is telling you that "there's more data than is being displayed."  So, if you added a formula off to the side and did a Length([field]) you should see that it truly is some very long length. You could also try dumping it to a .csv to confirm.

Hope that helps!

John

PS: actually, just double-click on one of the values in your Concat_ITEM_NO display, it should open the "Cell Viewer" and display the entire string for that cell.

[KaneG]

I'll also add to this that the screenshot is of the 'Browse Everywhere' which is a sample of the data at that point. That sample is restricted to 255 Characters per cell and a certain amount of total data (1MB by default, configurable in settings). 

Is the actual data truncated, or just the sample on the connector? The suggestion above by @JohnJPS referring to adding the formula tool will tell you this.

[dakeefe]

All, thank you for your responses!! I'll try to address them all:

@JohnJPS and @KaneG

I took your suggestions and added a formula to count the number of characters immediately following the concatenate. It's very strange...even though the formula returns the correct, un-truncated string length, the red error triangle still displays the message that the cell contains truncated values. This is regardless of what string data type I input into the left side of the Summarize: the summarize will always force the output to be the type V_WString and will always truncate when using the concatenate function. To clarify, I am using Alteryx Designer 11.0 See screenshot below:

 

@adm510 and @gc

Your suggestions to switch the data type to WString, specify a larger string size, and then use the Multi-Row formula tool to group by column A and join Row-1 to Row of column B is what got around this issue with the Summarize tool.

Once again, thank you all!!

[JohnJPS]

Hi @dakeefe

Regarding the message "This cell has truncated characters," Alteryx is just informing that the display on screen is incomplete.  The characters are truly there in the dataset, but just not displaying on screen, thus the warning message.

[davidhenington]

and if you copy/paste those results into a text editor, it will only paste the trunc'd records. 

So, the solution is to use a browse tool or write somewhere.