How to Split Row On New Line

Question

Currently have a one column custom SQL table that has multiple entries per row, separated by a new line. I would like to be able to split or break apart the multiple values so we are left with one normalized table/column that has all of the unique values. Any ideas?

Table looks like...

Row, Value

1, ABC
    GHI
2, DEF
3, GHI
    JKL
4, MNO
5, PQR
    STU
    VWX
6, YZ
    PQR

What I would like to get to is this, in a new table.

1, ABC
2, DEF
3, GHI
4, JKL
5, MNO
6, PQR
7, STU
8, VWX
9, YZ

Any ideas on how this could be done?

Answer

CSV file we used for this example:

FieldA

ABC\nDEF\nGHI

JKL

MNO\nPQR

STU

VWX\nYZ

I imported this file into Sisense and created three new custom fields (one for each "level" of the cell):

StrParts(FieldA + '\\n','\\n',1)

StrParts(FieldA + '\\n','\\n',2)

StrParts(FieldA + '\\n','\\n',3)

Result:

Then in a new Custom SQL Expression table, I ran this query to get the final result:

You can make the original table invisible as desired.

This is just one option. It's a fairly common scenario - you can look online for other SQL techniques to do it and translate the functions to Sisense's syntax as necessary.

One general note: You'll need to escape your newline characters by using "\\n" within our text functions.

Published 10-16-2021

Elasticube