If you have limited amount of years you can use the Attributecreator and the conditional parameter:
So 2019 can be:
If (2019+afscjrijstermin)<= 2019 - then set punten
And next one:
If (2019+afscjrijstermin)<= 2019 - then set punten
You can also use a CustomTransformer and a "Looper" - but that is a more advanced method and more suitable if you can not "hard-code 10 years"
Use the JarenInGebruik as the number of copies in a Cloner. Then create a new attribute called Year which is the sum of 2019 plus the copynumber from the cloner. Then an AttributeCreator to create a new attribute with the attribute name based on the Year, then aggregate by original row number.
This is relatively straightforward with SQL recursion. Luckily FME can do this with InlineQuerier using SQLite's WITH RECURSIVE Common Table Expressions.
- Send the features/table to InlineQuerier as "DataTable". Set the Field Data Types appropriately.
- Use the following SQL Statement to return the (unpivoted) Data
WITH RECURSIVE PuntenData AS
(SELECT rowID AS GroupID, 1*substr(Aanschafdatum,1,4) AS StartYear, 1*substr(Aanschafdatum,1,4) AS ReportYear, Afschrijftermijn AS RemainingPeriods, punten AS Value FROM DataTable
UNION ALL SELECT A.GroupID, A.StartYear, (A.ReportYear+1) AS ReportYear, A.RemainingPeriods-1 AS RemainingPeriods, A.Value AS Value FROM PuntenData A WHERE A.RemainingPeriods>1)
SELECT * FROM PuntenData WHERE ReportYear>2018 ORDER BY GroupID, ReportYear
This returns the data like so:
This can be fed straight into an FME AttributePivoter to pivot the ReportYear as a Column (Apologies, the sample data looked like the number of report years was based on Afschrijftermijn rather than JarenInGebruik ?)
To break this down, a WITH statement creates a temporary "View" of the data called "PuntenData". This View is made up of 2 SELECT statements being appended to each other:
- The first SELECT... statement creates a Starting Row of data that is the first Report Year, and initiates the data using the first year
- The second UNION SELECT... statement takes the previously calculated Row, and then recalculates based on the previous row's data. So the 2nd row of data will be calculated from the first row, the 3rd row of data will be calculated from the 2nd row etc. This part of the statement is Recursive: It keeps looping until a condition is no longer met. In this case, the Condition is "WHERE RemainingPeriods>1"
- The final SELECT * FROM PuntenData selects the results created within the Recursive View.
Use the JarenInGebruik as the number of copies in a Cloner. Then create a new attribute called Year which is the sum of 2019 plus the copynumber from the cloner. Then an AttributeCreator to create a new attribute with the attribute name based on the Year, then aggregate by original row number.
Thank you so much for the great explication.
The only thing I had to figure out was to concatenate the fields:-)
If you have limited amount of years you can use the Attributecreator and the conditional parameter:
So 2019 can be:
If (2019+afscjrijstermin)<= 2019 - then set punten
And next one:
If (2019+afscjrijstermin)<= 2019 - then set punten
Thank you, I tried to understand it but I could not....Friday mode on I guess;-)
This is relatively straightforward with SQL recursion. Luckily FME can do this with InlineQuerier using SQLite's WITH RECURSIVE Common Table Expressions.
- Send the features/table to InlineQuerier as "DataTable". Set the Field Data Types appropriately.
- Use the following SQL Statement to return the (unpivoted) Data
WITH RECURSIVE PuntenData AS
(SELECT rowID AS GroupID, 1*substr(Aanschafdatum,1,4) AS StartYear, 1*substr(Aanschafdatum,1,4) AS ReportYear, Afschrijftermijn AS RemainingPeriods, punten AS Value FROM DataTable
UNION ALL SELECT A.GroupID, A.StartYear, (A.ReportYear+1) AS ReportYear, A.RemainingPeriods-1 AS RemainingPeriods, A.Value AS Value FROM PuntenData A WHERE A.RemainingPeriods>1)
SELECT * FROM PuntenData WHERE ReportYear>2018 ORDER BY GroupID, ReportYear
This returns the data like so:
This can be fed straight into an FME AttributePivoter to pivot the ReportYear as a Column (Apologies, the sample data looked like the number of report years was based on Afschrijftermijn rather than JarenInGebruik ?)
To break this down, a WITH statement creates a temporary "View" of the data called "PuntenData". This View is made up of 2 SELECT statements being appended to each other:
- The first SELECT... statement creates a Starting Row of data that is the first Report Year, and initiates the data using the first year
- The second UNION SELECT... statement takes the previously calculated Row, and then recalculates based on the previous row's data. So the 2nd row of data will be calculated from the first row, the 3rd row of data will be calculated from the 2nd row etc. This part of the statement is Recursive: It keeps looping until a condition is no longer met. In this case, the Condition is "WHERE RemainingPeriods>1"
- The final SELECT * FROM PuntenData selects the results created within the Recursive View.
I had no time to test this but I will next week. Thank you so much for this explanation!
Thank you, I tried to understand it but I could not....Friday mode on I guess;-)
Sorry for being to detailed 
@ebygomm s solution is better and easier :)
