Magic ETL Possibility?

pjames1122

Hi, I'm having an issue. I want to split the fruit column by comma then alphabetize, then bring them back together on their respective account ID. How would I do that in magicETL?

ColemenWilson

There is a new tile in Magic ETL that is currently in Beta that would allow for this and make it MUCH easier. You can reach out to your CSM to see if you can join the beta for it.

Otherwise, you'll have to use a SQL dataflow and do the following:

1. Create a table in a MYSQL dataflow:
SELECT
Account ID,
CASE
WHEN LENGTH(Fruit) - LENGTH(REPLACE(Fruit, ',', '')) + 1 >= 1 THEN SUBSTRING_INDEX(Fruit, ',', 1)
ELSE NULL
END AS Fruit1,
CASE
WHEN LENGTH(Fruit) - LENGTH(REPLACE(Fruit, ',', '')) + 1 >= 2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(Fruit, ',', 2), ',', -1)
ELSE NULL
END AS Fruit2,
CASE
WHEN LENGTH(Fruit) - LENGTH(REPLACE(Fruit, ',', '')) + 1 >= 3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(Fruit, ',', 3), ',', -1)
ELSE NULL
END AS Fruit3,
CASE
WHEN LENGTH(Fruit) - LENGTH(REPLACE(Fruit, ',', '')) + 1 >= 4 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(Fruit, ',', 4), ',', -1)
ELSE NULL
END AS Fruit4,
CASE
WHEN LENGTH(Fruit) - LENGTH(REPLACE(Fruit, ',', '')) + 1 >= 5 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(Fruit, ',', 5), ',', -1)
ELSE NULL
END AS Fruit5
FROM
magic_etl_possibility

2. Create a second table in the dataflow:
SELECT
Account ID,
TRIM(Fruit1) AS column_value
FROM
transform_data_1
UNION ALL
SELECT
Account ID,
TRIM(Fruit2) AS column_value
FROM
transform_data_1
UNION ALL
SELECT
Account ID,
TRIM(Fruit3) AS column_value
FROM
transform_data_1

ORDER BY column_value ASC

3. Create the final table:

SELECT
Account ID,
GROUP_CONCAT(column_value SEPARATOR ', ') as 'Alphabetized Fruit!'

FROM transform_data_2
GROUP BY Account ID

4. Behold the final output:

pjames1122

@ColemenWilson , awesome thanks for the answer. I’ll give it a try. Also, name of beta tile so I can request?

ColemenWilson

No problem! It's called "Order Tile" for Magic ETL.

GrantSmith

A more straightforward method rather than multiple CASE WHEN statements is to use SPLIT_PART

SPLIT_PART(`Fruit`, ',', 1) -- First in CSV
SPLIT_PART(`Fruit`, ',', 2) -- Second in CSV
SPLIT_PART(`Fruit`, ',', 3) -- Third in CSV