Create a Calculated Field in Back End

[acme]

Hi all,

I would like to create new calculated fields from my existing data, but the problem is I need to make set analysis and summations in Data Load Editor, by using "Resident" expression.

In the following, you will see a representative tables,

[Energy]:

Year-Month	Electricity	Type
2022-1	100	Grid
2022-1	60	PV
2022-1	5	GenSet
2022-2	110	Grid
2022-2	40	PV
2022-2	3	GenSet

[Energy]:

Load

Year-Month,

Electricity,

Type

Will be created a new table with set expressions and Summations. What will be the code?

[TotalElectricity]: (to be created as a new table)

Year-Month	TotalElectricity
2022-1	165 (100+60+5)
2022-2	153 (110+40+3)

[sidhiq91]

@acme  Please see the below code used in the back end. Please let me know if this has helped.

NoConcatenate
Temp:
Load * Inline [
Year-Month, Electricity, Type
2022-1, 100, Grid
2022-1, 60, PV
2022-1, 5, GenSet
2022-2, 110, Grid
2022-2, 40, PV
2022-2, 3, GenSet
];

NoConcatenate
Temp1:
Load [Year-Month],
sum(Electricity) as Total_Electricity

Resident Temp
group by [Year-Month];

Drop table Temp;

Exit Script;

[acme]

Hi @sidhiq91 ,

Thanks for the script and solution. Actually it works in the sample code, but in real situation I have more complicated fields. Could you please have a look at below script? 

I need to create a summation for the "Grid", "PV", "Cogen" and "GenSet" as a new field. Ngas will be left alone.

Load * Inline [
Plant, Energy, Type, Month, Year,
A, 100, Grid, 1, 2022,
A, 50, PV, 1, 2022,
A, 30, Cogen, 1, 2022,
A, 10, Ngas, 1, 2022

A, 110, Grid, 2, 2022,
A, 57, PV, 2, 2022,
A, 29, Cogen, 2, 2022,
A, 11, Ngas, 2, 2022

B, 90, Grid, 1, 2022,
B, 21, PV, 1, 2022,
B, 12, GenSet, 1, 2022,
B, 7, Ngas, 1, 2022

B, 55, Grid, 2, 2022,
B, 19, PV, 2, 2022,
B, 6, GenSet, 2, 2022
B, 3, Ngas, 2, 2022];

[sidhiq91]

@acme  Could you please provide how the expected output should look like?

[acme]

@sidhiq91 

Electricity = (Grid + PV + Cogen + GenSet)

And groupped by plant and year-month.

The expected output should look like,

Year-Month	Plant	Electricity	Natural Gas
2022-1	A	180	10
2022-2	A	196	11
2022-1	B	123	7
2022-2	B	80	3

[acme]

@sidhiq91 Any idea would be appreciated. thanks

[MayilVahanan · Accepted solution]

Hi 

Try like below

Temp:
Load *, Plant&'|'&Month&'|'&Year as Key Inline [
Plant, Energy, Type, Month, Year,
A, 100, Grid, 1, 2022,
A, 50, PV, 1, 2022,
A, 30, Cogen, 1, 2022,
A, 10, Ngas, 1, 2022

A, 110, Grid, 2, 2022,
A, 57, PV, 2, 2022,
A, 29, Cogen, 2, 2022,
A, 11, Ngas, 2, 2022

B, 90, Grid, 1, 2022,
B, 21, PV, 1, 2022,
B, 12, GenSet, 1, 2022,
B, 7, Ngas, 1, 2022

B, 55, Grid, 2, 2022,
B, 19, PV, 2, 2022,
B, 6, GenSet, 2, 2022
B, 3, Ngas, 2, 2022
];

Load Key, Sum(Energy) as Electricity Resident Temp where match(Type, 'Grid', 'PV', 'Cogen', 'GenSet')
Group by Key;

Exp1: Sum(Electricity)

Exp2: Sum({<Type={'Ngas'}>}Energy)

[acme]

Thanks @MayilVahanan , it actually works!