Delimiter issue

MayaU_01
MayaU_01 Member
edited October 4 in Magic ETL

Hello Team,

I am trying to do a ETL in domo but unable to get desired result please suggest.

In my input, i have a few columns, the lowest grain in Employee id, I have a column called Value, In this values are given using delimiter "|". In my below example there are 2 instances on "|" but in my data it can come any no. of times.

My requirement was to create an output which separates the delimited values in new rows . I tried to use the split column in domo etl but i have to give the column names for each split, and the issue is that the delimiter can be used 100+ times or may not be used at all. But the query should work whenever we have a delimiter and get the values and place in a new row respective to the employee id and other relevant details . I am also unable to use any recursive function code in SQL tile. Please suggest.

Input

Entity

Name

Employee ID

Policy column

Value

user

Jack

1001

Country ID

JAPAN

user

Blake

1002

Country ID

Japan|DENMARK|Albania

user

Blake

1002

Client ID

Microsoft

Ouput

Entity

Name

Employee ID

Policy column

Value

user

Jack

1001

Country ID

JAPAN

user

Blake

1002

Country ID

Japan

user

Blake

1002

Country ID

DENMARK

user

Blake

1002

Country ID

ALBANIA

user

Blake

1002

Client ID

Microsoft

Tagged:

Answers

  • Have you tried using Magic ETL with Text tile, Split Column to split on the pipe symbol?

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • MarkSnodgrass
    edited October 4

    You will want to use this method that I outline in this video to break them up dynamically since you don't know how many there will be.

    @MayaU_01

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Hello, I tried the split column with pipe symbol, but then i have to add a column names for all my expected values. But as i explained i do not know the exact count, the raw data can come with 100+ delimited values and then this will fail

    Hope you are referring to this