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
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
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.
0 comments
