Skip to Content
0

Calculation between records of different unit of measure type

Jan 06, 2017 at 03:18 PM

58

avatar image

Hi community,

I have data with different unit of measures and want to do calculations between records using (graphical) views.

DATE, UNIT_NAME, VALUE_OIL, VALUE_GAS, VALUE_%
1, OIL, 50, ?, ?
1, GAS, ?, 100, ?
1, %, ?, ?, 50
2, OIL, 100, ?, ?
2, GAS, ?, 30, ?
2, %, ?, ?, 50

Calculations (not only aggregation), e.g.

- OIL + GAS

- OIL * %

Output:

DATE, OIL+GAS, OIL*%
1, 150, 25
2, 130, 50

Is there an elegant way to do this?

All I can think of at the moment is transponing the source data to:

DATE, NAME_OIL, NAME_GAS, NAME_%, VALUE_OIL, VALUE_GAS, VALUE_%
1, OIL, GAS, %, 50, 100, 50
2, OIL, GAS, %, 100, 30, 50

With this record I can use calculated columns to make above calculations within this record.

Is there any more elegant way to do this, preferably using graphical views?

IMPORTANT: the 'UNIT_NAME' columns are exemplary for Unit of Measure information for each KPI that needs to be kept for unit of measure conversions. Unfortunately this column stops me from being able to just aggregate the records with the same DATE to one record.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Prasad AV Jan 06, 2017 at 09:57 PM
0

Hi Jan,

You can use Union in the Graphical CV sourcing from 3 nodes;

1st Node - Filter on 'OIL' - Output Fields --> Date & VALUE_OIL

2nd Node - Filter on 'GAS' - Output Fields --> Date & VALUE_GAS

3rd Node - Filter on 'NAME_%' - Output Fields --> Date & VALUE_%

Union them with DATE being the common mapping among the three nodes and add the above 3 measures Viz, VALUE_OIL, VALUE_GAS and VALUE_% as target columns at the union node; essentially transposing it to a single record with date and other three measures;

Flow will look like the following diagram:

Hope this helps;

Cheers,

Prasad A V


jan.png (25.9 kB)
Share
10 |10000 characters needed characters left characters exceeded