Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Splitting column by delimiter without retrieving blank columns

Hi guys,   i am splitting a text column by a text delimiter. Delimiter in my case is the text "CAUTION: ". After the split i retrieve 1000+ columns with Null values in each cell. I would actually o...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Are you sure that none of the cells has multiple instances of "CAUTION:" in it? If just one has multiple instances, then you'll get more columns than you're expecting.

     

    I'd recommend splitting it into rows instead of columns to track down where the multiple "CAUTION:" values are coming from. So, instead of

    Splitting into this:

    You split it like this:

    After this step, you can group on the DateTimeReceived to see which ones are responsible for the duplicate values.

    It'll be the one(s) with a count higher than most of the rest.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

Are you sure that none of the cells has multiple instances of "CAUTION:" in it? If just one has multiple instances, then you'll get more columns than you're expecting.

 

I'd recommend splitting it into rows instead of columns to track down where the multiple "CAUTION:" values are coming from. So, instead of

Splitting into this:

You split it like this:

After this step, you can group on the DateTimeReceived to see which ones are responsible for the duplicate values.

It'll be the one(s) with a count higher than most of the rest.