Skip to main content
  • EN

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

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.

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.

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

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.

 

 

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

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 !

 

Reply


Community Stats

30,185
Posts
114,572
Replies
37,848
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.