expand a range/series into columns

[kit_kat]

Hi there - learning Knime! and I am totally stuck on an item.
Here is the Knime data I have gotten up to:

CustomerID, and range of years:

And I want to get to a final solution of :

Problem:
So for example customerID 8152 has set year ranges defined as: 2011-2011, 2011-2014, 2013-2015:

Solution
2011-2011 → column 2011 and cell gets populated with 2011
2011-2014 → columns 2011, 2012, 2013, 2014 and cells get populated.
2013-2015 → columns 2013, 2014, 2015 and cells get populated.

Note: There could be year gaps for some customer IDs. So its not necessary that a Customer ID will always have contigious years, but could have a gap year.
Totally lost on how to approach this problem.
I am a beginner with Knime ( learning on my own time for a non-profit org).

Any help would be great!

[rfeigel]

@kit_kat You’re more likely to get help if you post data rather than screenshots.

[ArjenEX]

To add what @rfeigel already mentioned, in the first screenshot that is cut-off at the top, it looks like a unique concatenate aggregation is used to create that string.

I have a feeling that getting your desired result is better achievable without this since one of the first steps you would have to take to get to a solution is to ungroup the list back again to analyse the individual date ranges.

So the more your can provide in a workable format, the better the chances are of getting the desired help

[kit_kat]

I agree with both of you. I will attempt to clarify the problem statement, and also provide better ‘ungruoped’ version of the data.
Thanks

[takbb]

Hi @kit_kat, expansion of your years can be performed with a combination of cell splitters and ungrouping.

Generation of the range data (i.e. the years between start and end of ranges) can be performed using “math formula”, “rank” and “one row to many” nodes

and the subsequent tabulation of the data can be performed using Pivot.

Expand Ranges and tabulate data.knwf (28.1 KB)

[kit_kat]

Hi everyone - sorry for the delay !
Here is how the data looks like before its grouped:

CustID fromYear toYear
CustX 2011 2011
CustY 2018 2019
CusX 2020 2021
CustA 2022 2023
CustY 2023 2023

And it needs to expand it a pivot/series:

Years 2011 2012 2013 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024…2099

CustID
CustA would get populated '2022 under 2022 column and ‘2023’ under 2023 column
Cust X would get populated under 2011, 2020, 2021 columns
CustY would get populated under 2018, 2019, 2023 columns.

Hope this helps!

[kit_kat]

This worked beautifully! wow - you are a genius.

Thank you so much!