Converting a single comma separated row into multiple rows in QuickSight

[Biswajit_1993]

Hi Team,
Can we have any functionalities available in QuickSight which help us to splitting the comma separated single column into multiple column.

I read some answer on this topic in QuickSight so currently this functionalities are not available in QuickSight.

Thanks & Regards
Biswajit Dash

[Max]

Hey @Biswajit_1993

Does this help?

New Line (force wrap) in pivot cell

I have table I don’t want to show 3 levels of columns. I have a calculated field that aggregates the three keys as 1 column as a hidden sort key. {A}{B}{C} Now I want to place in the 2nd column unhidden the 3 hierarchy names wrapped in the cell on new lines. So in the first visual cell: “Level A”: {A} “Level B”: {B} “Level C”: {C} Providing a function returning (“Level A”: {A}\n"Level B": {B}\n"Level C": {C}) doesn’t work. These can be longer descriptions and I’ll never know how long th…

[Sanjeeb2022]

Hi @Biswajit_1993 - Couple of things,

1. Is your input string having constant number of commas? If yes, it is easy to use substring and extract the require columns.
2. If you are looking for specific columns like first and second, same approach can be implement like step -1 and extract the details.
3. If your number of comma are not constant and it vary, its top ( i guess each row may have different numbers).

Please provide input and expected output so that we can explore the solution.

Regards - San

[Biswajit_1993]

Hi @Sanjeeb2022, @Max I have attached a sample snap created by me for you to understand my query in details but my actually dataset is different with more value present in a single rows.

So any advice from both of you how can I handle in this type of scenarios.

Thanks & Regards
Biswajit Dash

[Sanjeeb2022]

Hi @Biswajit_1993 - Thanks for the details but the input is bit confusing, for example for the second row you are looking for the value where the input field do not have that value.

Let’s connect and understand the requirement. Will connect with you offline.

Regards - San

[Biswajit_1993]

hi @Sanjeeb2022 if you see the first cell the value is like 111,222,333 then in the second cell the value is like 444 in the 3rd 555 and in the 4th 666 so these are the value I want to extract.

Thanks & Regards
Biswajit Dash

[Max]

You can extract one from a calculated field. But you cannot add a row in quicksight.

Calculated fields only create new columns.

This can be accomplished via sql. For instance if you are using presto you can cast it to an array for all columns and then use the unnest function.

[Kristin]

Hi @Biswajit_1993. Did Max’s reply help? Hope so!

[Biswajit_1993]

Hi @Kristin yes its help me as suggested by @Max & @Sanjeeb2022 I handled the query at database end so now its working fine.

Thanks & Regards
Biswajit Dash