Split array values into multiple rows

mohd_amzad9559 — Wed, 13 Nov 2024 07:07:55 GMT

I have .txt file where data looks like below:

actionid | year | value
123 | [2024,2025] | [16,17]

I need these record to split in two rows

actionid | year | value
123 | 2024 | 16

123 | 2025 | 17

Thanks

Re: Split array values into multiple rows

madelonjansen — Wed, 13 Nov 2024 10:27:36 GMT

ohh, this is a nice one! You'll have to go row by row to get this right.

First; determine how many 'parts' there are in the year or value field, by using Substringcount(year, ',')+1
(the number of comma's, plus 1)

Then use a while loop and iterno() in a preceding load to match all of the parts using subfield:

load
actionid,
subfield(year, ',', iterno()) as year,
subfield(value, ',', iterno()) as value
While iterno()<=i;
load
actionid,
year,
value,
substringcount(year, ',')+1 as i
From file;

topic Re: Split array values into multiple rows in Data Quality

Split array values into multiple rows

Re: Split array values into multiple rows