cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

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:

Community_Admin_0-1634393472490.png

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

Community_Admin_1-1634393472469.png

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.

Rate this article:
Version history
Last update:
‎10-16-2021 07:11 AM
Updated by:
Contributors