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.
I need to iterate a column when I clicked on a button will you help me with this question
The column contain side by side layouts
Example need to add employee information which contain I'd,name,mail inbsid by side layouts
This is a very old thread and your new question seems like it might not be very closely related, so I would suggest starting your own new thread wherein you provide more details including your use case, what you've tried and what roadblocks you've hit, as well as perhaps some sample SAIL code and/or screenshots.
sure mike