Any way to normalize a column of data?

user06327
user06327
in Archive

Hi everyone,

 

I'm working on a dataset that has an attribute structured with a list of sub-attributes and values, which are separated by colon or coma respectively like this:Screenshot 2018-09-23 12.58.09.pngDoes anyone know which tools in Domo can I use to flatten this?

I hope this could be like the following instead:

Screenshot 2018-09-23 13.12.26.png

 

Comments

  • Unknown
    Unknown

    Hi, user06327,

     

    You can probably accompish this in either SQL or ETL dataflows in Domo. You can think of the task one of two ways:

    1) extract substrings for the various fields you want to capture. This can be done in a SQL dataflow. e.g. (pseudocode):

    substring_index(`accelleration`,',',1) -- returns the string from the beginning to the first comma

    2) replace all the text with an expty string except for the part you want to keep. Going this route, you can use the "Replace Text" block in ETL. You'll have the option of using regular expressions, too, though looking at your dataset, that probably won't be necessary.

     

    In either case, you'll end up repeating essentially the same process for each of the various fields you want to extract.

     

    Check out this Knowledge Base article, which should give you the details you'll need: https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Pulling_a_Segment_of_Text_from_a_Text_String

     

    Cheers

Categories