splitting one row into many row according to range data

[enricocamerin]

Hello,

I have this table

original

Cod   quantity  data_start   date_end

DEU0042  , 12, '01-01-2020', '31-12-2020'

and I need to split in this one

Cod   quantity  month   year

DEU0042 ,1,1,2020

DEU0042 ,1,2,2020

DEU0042 ,1,3,2020

repeat for every month

DEU0042 ,1,12,2020

thanks for your help

Enrico

[MartW]

@enricocamerin , you can try and use the subfield function in combination with substringcount function to see how many separators there are and to split them into a column. 

after that you can add this to a loop to get the table you want.

[Or]

This would be a typical use case for Load... While

Syntax might be a little off, but generally speaking, and assuming the fields are read as valid dates:

Load Cod, quantity, AddMonths(data_start,IterNo()-1) as Date INLINE [

Cod   quantity  data_start   date_end

DEU0042  , 12, '01-01-2020', '31-12-2020'

]

While AddMonths(data_start,IterNo()-1) <= date_end;

You can split the Date section into month or year or whatever else you need based on the same calculation.