Hi Team,
I have dataset where i have list of cloumn . I want to perform aggeration in that.
I have written some code but further i am not able to write , can anyone help me on that , what to add next .
Dataset : FiltersHide section contents[Asset:House,A:0,B:0,C:0,D:14]; [Asset:House,A:0,B:0,C:0,D:6]; [Asset:House,A:0,B:0,C:0,D:2]; [Asset:House,A:0,B:0,C:0,D:62]; [Asset:House,A:0,B:0,C:0,D:2]; [Asset:House,A:0,B:0,C:0,D:1]; [Asset:House,A:0,B:0,C:0,D:67]; [Asset:House,A:0,B:1,C:0,D:0]; [Asset:House,A:0,B:0,C:0,D:10]; [Asset:House,A:0,B:0,C:0,D:112]; [Asset:House,A:0,B:0,C:0,D:6]; [Asset:House,A:0,B:0,C:0,D:12]; [Asset:House,A:0,B:0,C:0,D:86]; [Asset:House,A:0,B:0,C:0,D:3]; [Asset:House,A:0,B:0,C:0,D:3]; [Asset:House,A:0,B:0,C:0,D:2]; [Asset:House,A:0,B:0,C:0,D:2]; [Asset:Car,A:0,B:0,C:0,D:2]; [Asset:Car,A:0,B:0,C:0,D:46]; [Asset:Car,A:0,B:0,C:0,D:53]; [Asset:Car,A:0,B:0,C:0,D:2]; [Asset:Car,A:0,B:0,C:0,D:3]; [Asset:Car,A:0,B:0,C:0,D:3]; [Asset:Car,A:0,B:0,C:0,D:1]; [Asset:Car,A:0,B:0,C:0,D:2]; [Asset:Car,A:3,B:0,C:0,D:0]; [Asset:Car,A:0,B:0,C:0,D:47]; [Asset:Car,A:0,B:0,C:0,D:49]; [Asset:Car,A:0,B:0,C:0,D:2]; [Asset:Land ,A:0,B:0,C:0,D:1]; [Asset:Land ,A:0,B:0,C:0,D:1]; [Asset:Land ,A:0,B:0,C:0,D:1]; [Asset:Land ,A:0,B:0,C:0,D:1]; [Asset:Land ,A:0,B:0,C:0,D:1]; [Asset:Land ,A:0,B:0,C:0,D:1]; [Asset:Land ,A:0,B:0,C:0,D:1]; [Asset:Land ,A:0,B:0,C:0,D:1]; [Asset:Land ,A:0,B:0,C:0,D:1]; [Asset:Land ,A:0,B:0,C:0,D:3]
local!test: a!forEach( items: local!assetDataSet.data,expression:
{
asset:local!dataSet.Asset,A: sum(fv!item.A),--This sum operation should work at asset level , in this its not working as i am iterating all the rows, so same data is coming . But i want to return limited set of rows for each asset only.B: sum(fv!item.B),C: sum(fv!item.C),D: sum(fv!item.D)/*total:sum(sum(fv!item.A),sum(fv!item.B),sum(fv!item.C),sum(fv!item.D))*/
} ) ) )
I want dataset from this forEach loop where all respective items should be sum at Asset level.
I mean
Asset
asset:House A:10 B:20 c:30 D:40 Total:100
asset:Land A:10 B:20 c:130 D:40 Total:200
asset:Car A:10 B:20 c:230 D:40 Total:300
Please ignore data, that i just added here randomly for more clarity.
Kindly help me to resolve my issues.
Discussion posts and replies are publicly visible
Hi @sauravk,
Below is a sample of some code you can use to work from. The main point is you need to first get a list of your assets and then perform your forEach on the distinct assets to perform your calculations for each asset.
with( local!assetDataSet: a!dataSubset( data: { { Asset: "House", A: 5, B: 0, C: 0, D: 14 }, { Asset: "House", A: 11, B: 4, C: 0, D: 6 }, { Asset: "House", A: 0, B: 0, C: 0, D: 2 }, { Asset: "Car", A: 0, B: 0, C: 0, D: 2 }, { Asset: "Car", A: 0, B: 0, C: 0, D: 46 }, { Asset: "Car", A: 0, B: 0, C: 0, D: 53 }, { Asset: "Land", A: 0, B: 0, C: 0, D: 1 }, { Asset: "Land", A: 0, B: 0, C: 0, D: 1 } } ), local!distinctAssets: union( local!assetDataSet.data.Asset, local!assetDataSet.data.Asset ), local!test: a!forEach( items: local!distinctAssets, expression: { asset: fv!item, A: sum( index( local!assetDataSet.data.A, wherecontains( fv!item, touniformstring( local!assetDataSet.data.Asset ) ), 0 ) ), B: sum( index( local!assetDataSet.data.B, wherecontains( fv!item, touniformstring( local!assetDataSet.data.Asset ) ), 0 ) ), C: sum( index( local!assetDataSet.data.C, wherecontains( fv!item, touniformstring( local!assetDataSet.data.Asset ) ), 0 ) ), D: sum( index( local!assetDataSet.data.D, wherecontains( fv!item, touniformstring( local!assetDataSet.data.Asset ) ), 0 ) ), total: sum( index( local!assetDataSet.data.A, wherecontains( fv!item, touniformstring( local!assetDataSet.data.Asset ) ), 0 ), index( local!assetDataSet.data.B, wherecontains( fv!item, touniformstring( local!assetDataSet.data.Asset ) ), 0 ), index( local!assetDataSet.data.C, wherecontains( fv!item, touniformstring( local!assetDataSet.data.Asset ) ), 0 ), index( local!assetDataSet.data.D, wherecontains( fv!item, touniformstring( local!assetDataSet.data.Asset ) ), 0 ) ) } ), local!test )
I have another problem. Like i am passing one dataset to processmodel where we are calling one rule which is preparing data for export functionality. For that i am passing dataset which i am taking in process variable and then further passing to rule in o/p variable. But when i am running my code n trying to down load excel its breaking and giving some error. I have monitored process model, dataset is not coming on process modal. Do you have any idea where i am missing n what could be the root cause. This excel export was working fine earlier as per req i had need to pass my dataset in that, which i am using further on rule. Sail Code load( local!testUri: fn!getdatasubsetdownloadlinkfromprocess( processModelUuid: cons!TEST_UUID, dataset: ri!assetDataset ), with( local!fullsubset: rule!getDataSet(), ri!assetDataset:local!fullsubset.data *** a!linkField( links: a!safeLink( uri: local!testUri, label: "Test to Excel" ) ) *** ) ) I checked input variable also on both side. Both side i have kept same name same type n selected multiple on process model. Error: There is a problem with task “Generate excel” in the process “Test”. ERROR:An error occurred while evaluating expression: exportData:rule!prepareExcelData(pv!summaryExcelDataset) (Expression evaluation error at function rule!prepareExcelData: Invalid index: Cannot index property 'data' of type Text into null value of type AssetDataType?list) (Data Outputs) Problem: An invalid expression has been encountered in a task. Recommended Action: Check the expression including any function requirements and resume. Priority of this problem: High Priority
Tried to call same rule on sail code also but getting below error.
local!test:rule!assetToExcel(assetDataset:ri!asset.data) ,
Could not display interface. Please check definition and inputs. Interface Definition: Expression evaluation error at function rule!assetToExcel[line 156]: Invalid index: Cannot index property 'data' of type Text into type AssetDataType?list
Please suggest if you got any idea on this.