Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
enricocamerin
Creator
Creator

splitting one row into many row according to range data

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

 

 

Labels (1)
2 Replies
MartW
Partner - Specialist
Partner - Specialist

@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
MVP
MVP

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.