Split array values into multiple rows

[mohd_amzad9559]

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

[madelonjansen]

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;