Skip to Content
T J

Calculation between records of different unit of measure type

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Jan 06, 2017 at 09:57 PM

    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

    Add comment
    10|10000 characters needed characters exceeded