Skip to main content
Solved

Working across Different tables

  • August 14, 2017
  • 6 replies
  • 14 views
A

I have data stored across multiple DBF files.

DBF1 contains data stored as follows (sample):

ID ---------DEMAND-------PATTERN

1123-----------5---------------0.3455

3456-----------3---------------0.3212

2322-----------2---------------0.2112

DBF2 contains data stored as follows (sample):

ID----------------ZONE

1123------------ZONE1

3456------------ZONE2

2322------------ZONE1

TOTAL_DEMAND for a particular zone is calculated as the sum of products of the demand and pattern for all the ID that lie within that ZONE.

Ex. for ZONE1(which contains 1123 and 2322), TOTAL_DEMAND= 5*0.3455+2*0.2112

I need to create the final results tabulated as follows:

ZONE-------------TOTAL_DEMAND

ZONE1-----------(calculated as above)

ZONE2-----------(calculated as above)

The task is easier with VLOOKUP in excel, but I am not able to find suitable transformers to do the same in FME. Please suggest a suitable method.

Suggestions regading use of a programming language(such as python) to achieve the same are also welcome.

PS- Hiphens (-) are added just to give clarity about the data arrangement

Best answer by takashi

If you are familiar with SQL statements, the InlineQuerier could also be a choice. e.g.

0684Q00000ArJqeQAF.png

select
    a.ZONE,
    sum(b.DEMAND * b.PATTERN) as TOATL_DEMAND
from DBF2 as a
inner join DBF1 as b on b.ID = a.ID
group by a.ZONE
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

6 replies

fmelizard
Safer
Forum|alt.badge.img+21
  • fmelizardSafer
  • Safer
  • August 14, 2017

Hi @amitaman001,

 

 

I suggest you try using the FeatureMerger of DataBaseJoiner to merge the data based on ID. This will then enable you to easily use either an ExpressionEvaluator or AttributeCreater to create a new TOTAL_DEMAND attribute based on the math above.

erik_jan
Contributor
Forum|alt.badge.img+23
  • erik_janContributor
  • Contributor
  • August 14, 2017

Hi @amitaman001

I would use a StatisticsCalculator on DBF1 to calculate the TOTAL_DEMAND (group by ID): use the Sum calculation and remove all other output..

Then use the FeatureMerger to join DBF2 based on ID = ID.

That should get you the required output.

danilo_fme
Celebrity
Forum|alt.badge.img+52
  • danilo_fmeCelebrity
  • Celebrity
  • August 14, 2017

Hi @amitaman001,

I created a template file using .xls like Reader.

How can you see, i used first the transformer FeatureMerger by attribute ID. After, used the ExpressionEvaluator to calculate _result ( @Value(DEMAND)*@Value(PATTERN) ).

At last the transformer Aggregator by Zone.

Thanks,

Danilo - -total-demand.fmwt

Partner Solutial Brazil - www.solutial.com.br
erik_jan
Contributor
Forum|alt.badge.img+23
  • erik_janContributor
  • Contributor
  • August 14, 2017

Hi @amitaman001

I would use a StatisticsCalculator on DBF1 to calculate the TOTAL_DEMAND (group by ID): use the Sum calculation and remove all other output..

Then use the FeatureMerger to join DBF2 based on ID = ID.

That should get you the required output.

After re-reading it should be the other way around:

 

Do the FeatureMerger to join, then the StatisticsCalculater and group by on ZONE.

 

 

takashi
Celebrity
  • takashiCelebrity
  • Best Answer
  • August 14, 2017

If you are familiar with SQL statements, the InlineQuerier could also be a choice. e.g.

0684Q00000ArJqeQAF.png

select
    a.ZONE,
    sum(b.DEMAND * b.PATTERN) as TOATL_DEMAND
from DBF2 as a
inner join DBF1 as b on b.ID = a.ID
group by a.ZONE
Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
A
  • amitaman001
  • Author
  • August 15, 2017

If you are familiar with SQL statements, the InlineQuerier could also be a choice. e.g.

0684Q00000ArJqeQAF.png

select
    a.ZONE,
    sum(b.DEMAND * b.PATTERN) as TOATL_DEMAND
from DBF2 as a
inner join DBF1 as b on b.ID = a.ID
group by a.ZONE
Thanks !

 

It works perfect !

 

Community Stats

32,154
Posts
122,047
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.