Split row on new line

Answered

Comments

4 comments

  • Avatar
    Sam Ovenshine

    Hi Christopher,

    This would require a bit of SQL. You'd want to use Sisense's text functions to split each entry in the cell into its own column. Then you'd SELECT each of the three columns (with a max of three items per cell in your example) and UNION each together.

    1
    Comment actions Permalink
  • Avatar
    Christopher Tangora

    In production we would have longer character length and number of records per each entry.

    Any chance you can share some of that SQL?  Not advanced enough (yet) to get it working with the description. 

     

    0
    Comment actions Permalink
  • Avatar
    Sam Ovenshine (Edited )

    Hi Chris,

    Sure. Below is a sample simple .CSV file I 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.

    Hope that helps.

    0
    Comment actions Permalink
  • Avatar
    Christopher Tangora

    That is so helpful, thank you!

    0
    Comment actions Permalink

Please sign in to leave a comment.