forEach loop issue

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

Parents
  • 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
    )

  • Thanks a lottttttt. It really helped a lot.
    One thing more in future if i face similar kind of issues , for that can message you on community ,if yes then how ?
  • If is related to this topic, just reply in this same thread and I will see it. For other non-related issues, I would just keep posting discussions as you have here.
  • 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.

  • It looks like your input is not in a DataSubset format. For your second example when calling the rule from SAIL, what data type is ri!asset? By having ri!asset.data, you are indicating its a DataSubset but the error seems to indicate it is not able to find '.data'.

    Perhaps ri!asset is already type 'AssetDataType'? If that is the case, you do not need '.data'
Reply
  • It looks like your input is not in a DataSubset format. For your second example when calling the rule from SAIL, what data type is ri!asset? By having ri!asset.data, you are indicating its a DataSubset but the error seems to indicate it is not able to find '.data'.

    Perhaps ri!asset is already type 'AssetDataType'? If that is the case, you do not need '.data'
Children
  • In the first I tried with passing datasubset format also same issue coming, no value is getting assign in PV . Second I got it. I will try for second but second is not an req that I am doing for testing purpose why that rule is not getting call. Apart of datasubset issue anything else clicking ?? As that already tried. Along back I faced similar kind issue then I tried with activity variable in process model instead to pass pv in rule. But that workaround is also not working. Is there any procedure to debug this where is the catch ? As on process model I am not displaying on firm just passing in rule n getting export in excel.
    Thanks for looking my problem in advance.