Question

Manipulation of an complex excel file

  • 5 September 2017
  • 7 replies
  • 2 views
D

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

7 replies

danilo_fme
Rank
Userlevel 4
Badge +30

Hi @delgadojeaneth,

Could you share us a sample you excel?

Thanks,

Danilo

erik_jan
Rank
Userlevel 2
Badge +16

Checking out the FME Desktop training for Excel recorded training session could give you some additional ideas.

D

Hi @delgadojeaneth,

Could you share us a sample you excel?

Thanks,

Danilo

Hi Danilo,

 

 

Here it is the file test2.xlsx.

 

 

Thank you very much

 

 

fmelizard
Rank
Userlevel 4
Badge +13
Hi @delgadojeaneth, are you able to provide a few more details on your workflow? Can you provide an example of the input and expected output?

 

steveatsafe
Rank
Badge +11
Hi @delgadojeaneth, are you able to provide a few more details on your workflow? Can you provide an example of the input and expected output?

 

Check the reply on danilo_inovacao comment's. delgadojeaneth has replied with the Excel File example.

 

 

SteveatSafe
G
Badge +3

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.

takashi
Rank
Userlevel 2
Badge +17

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.

0684Q00000ArKJoQAN.png

Then, add an InlineQuerier and set SQL statement according to the requirement.

0684Q00000ArKRRQA3.png

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"

0684Q00000ArKX3QAN.png

See also the attached demo: manipulate-complex-excel-table-example.fmwt (FME 2017.0)

Hope this helps.

Reply


Community Stats

28,711
Posts
109,487
Replies
36,559
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.