Delimiter issue

MayaU_01 · October 4

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

ArborRose · October 4

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

MarkSnodgrass · 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

https://youtu.be/kxjJ0T4Q4OE

MayaU_01 · October 4

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

Delimiter issue

Answers

Categories