Hi, I have a table column that has multiple values displayed like the following:
How do I split the text so that I have Brand, Data, and Products in separate rows using Table Transformer? I found an example but the delimiter is ",". In my case, the delimiter is the line break.
Here is what I found: https://docs.stiltsoft.com/tfac/dc-server/splitting-cell-values-to-different-rows-flatten-an-array-188621459.html
Any help would be appreciated.
Thank you.
Hi @Roberta Bortolotti ,
Please check this Community thread for reference: https://community.atlassian.com/t5/Confluence-questions/Split-based-on-New-line-character-in-Table-Transformer/qaq-p/2909807#U2911103
Thank you, @Stiltsoft support
I tried adding 3 spaces at the end of each option to see if I could split this way -- no luck.
Is it possible to use a combination of FORMATWIKI with the split function? Just brainstorming out loud.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The FORMATWIKI function is used to format the contents of your cells (make the text bold, color the background, and so on).
The SPLIT function works with strings and splits them by visible symbols, for example, commas, points, spaces (not bullets and new lines).
So, if you manually add a space after each word, the string will be splitted.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you, @Stiltsoft support
The split with spaces didn't work. Not sure why though. Any thoughts?
Also, can I combine MATCH_REGEXP with SPLIT?
Thank you again.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
And have you adjusted the initial SQL query from the example accordingly?
SEARCH / AS @a EX('Skills'->split(" ")) /
RETURN(@a->'Name' AS 'Name', _ AS 'Skills') FROM T1
Also you may find the list of the supported functions and more examples in our documentation.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Not sure why, but it didn't work. I have 3 spaces in the split function. The 'New are concern' column shows empty.
Here is my statement:
SEARCH / AS @a EX('Key Area of Concern'->split(" ")) /
RETURN(@a->'New area concern' AS 'New area concern', _ AS 'Key Area of Concern') FROM T1
Any thoughts?
Thank you,
Roberta
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Leave only one space in the split(" ") function, not two, three or more.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
In this case I can't have a bullet point with an option that is a composed term. For example, "New Products".
Are you saying it cannot split based on multiple spaces?
Thank you
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes, you are correct. You may use an underscore for strings with several words though: New_Products.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
That's not really helpful actually.
Let me know if you have any other suggestion.
Thank you
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You may try to follow the Excel guidelines about creating "real tables" (not just any data placed inside a table): if the mentioned Brand, Data, and Products are not just random words from the example but categories (each item has its own brand, some description, this or that product), then each category should be placed in a separate column.
Later these columns can be separately filtered, sorted, aggregated, and so on.
The workaround with splitting arrays isn't even a pure SQL function cause SQL databases and tables are not organized like this. This workaround with a bit of JavaScript is used to split arrays - objects that are used to store multiple values in a single variable. These values are always comma-separated. But for manually created tables you can also use points and other visible symbols.
So, another workaround may be to put "," or "." after each item that you need to split:
But it is not a best practice cause if you miss the separator, the items won't be splitted.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.