Hello,

I am struggling with transforming a dataset that I have to the desired format.

I have a table that looks (for example) like this:

ID;16;50;60;75

X1;10;25;80;95

X2;30;30;90;

X3;25;;80;100

The first issue is that I need to use the column headers to get classes:

0-16, 16-50, 50-60, 60-75

But for ID X3 the value in column 50 is missing and therefore I need to get the class 16-60 (instead of 16-50 as is the case for ID X1 and X2).

I don't know how to create this in FME.

Secondly, I need to calculate the value for each class by substracting the values from each other. So for ID X1 I need the following result:

Class 0-16 = 10

Class 16-50 = 15

Class 50-60 = 55

Class 60-75 = 15

Class >75 = 5

Besides, I want the classes as an upper- and lower limit column.

So the final result that I need for the three ID's is:

ID;UPPER_LIMIT;LOWER_LIMIT;VALUE

X1;16;0;10

X1;50;16;15

X1;60;50;55

X1;75;60;15

X1;;75;5

X2;16;0;30

X2;50;16;0

X2;60;50;60

X2;;60;10

X3;16;0;25

X3;60;16;55

X3;75;60;20

X3;;75;0

I hope my explanation makes sense. The attachment might make it a bit more visual.

Thanks in advance!

Eva