Hi,
I am new in FME. I have an excel file, that have a complex format, because base on Sub-Area, I have to only take certain parameters to do calculations. For example, for sub-area P01 I should get: Total cost, A+B and T+U and the same for P02. Something important to mention is that I can have several subareas and grouping the data have been a challenge for the way that the I get the data.
I hope somebody can help me,
Thanks
Hi @delgadojeaneth,
Could you share us a sample you excel?
Thanks,
Danilo
Checking out the FME Desktop training for Excel recorded training session could give you some additional ideas.
Hi @delgadojeaneth,
Could you share us a sample you excel?
Thanks,
Danilo
Here it is the file test2.xlsx.
Thank you very much
You can use variable setters to do that.
At the reader parameter set has filed name row to "no".
Test and set variable
if column A= sub area name then value = column B
Same for row total cost.
Then come the costs which u can use for instance stringsearcher regexp [A-Z]{1} and then set variable to column A and v
If all sub areas are stacked in same column range, then your done. If not then you must adres the columns with mod (nr-of-columns-per_subarea). Depending on how many you have you might want to turn the column letters to a value by concatenating the character codes.
Hi @delgadojeaneth, I would add appropriate Sub-Area Name to every data row and then create desired table with an InlineQuerier (SQL query statement).
You can use the VariableSetter/VariableRetriever to add Sub-Area Name to data rows, and modify field names with the AttributeManager if necessary.
Then, add an InlineQuerier and set SQL statement according to the requirement.
SQL Query Example
select * from ( select "Sub-Area", 'Total Cost' as "Item", "Cost", "Labour Cost", "Total", "Volume", "Total Cost" from source where "Item" = '_total' union all select "Sub-Area", 'A + C', sum("Cost"), sum("Labour Cost"), sum("Total"), sum("Volume"), sum("Total Cost") from source where "Item" in ('A', 'C') group by "Sub-Area" union all select "Sub-Area", 'Q + S', sum("Cost"), sum("Labour Cost"), sum("Total"), sum("Volume"), sum("Total Cost") from source where "Item" in ('Q', 'S') group by "Sub-Area") order by "Sub-Area"
See also the attached demo: manipulate-complex-excel-table-example.fmwt (FME 2017.0)
Hope this helps.