Calculation between records of different unit of measure type

[Former Member]

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.

[Former Member]

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