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
