EUC Excel Agg Output Template.xlsxEUC Excel.xlsx

Hi,

I have a requirement to read excel file and perform some aggregations on excel data.

I have created an expression rule to do the logic after parsing excel data to cdt but not working as expected.

no issue in reading /parsing excel data

Rule1 - 

EUC_CalculateData

a!localVariables(
local!hg: a!forEach(
items: ri!excelData,
expression: append(
ri!newList,




rule!EUC_CalculateGroupbyName(
excelData: fv!item,
newList: ri!newList
)
)
),
ri!newList
)

Rule 2- 

EUC_CalculateGroupbyName

a!localVariables(
if(
and(
not(
isnull(
ri!newList
)
),
length(wherecontains(
ri!excelData.Name,
ri!newList.Name
)) > 0
),
updatearray(
ri!newList,
wherecontains(
ri!excelData.Name,
ri!newList.Name
),
{
Name: ri!excelData.Name,
Total: dollar(sum(
if(isnull(ri!newList.Amount_1),0,ri!newList.Amount_1),
if(isnull(index(ri!newList,wherecontains(
ri!excelData.Name, ri!newList.Name
)).Amount_1),0,index(ri!newList,wherecontains(
ri!excelData.Name, ri!newList.Name
)).Amount_1)
))
}
),
append(
ri!newList,
'type!{urn:com:appian:types}EUC_ExcelData'(
Name: ri!excelData.Name,

Total: dollar(
if(
isnull(
ri!excelData.Amount_1
),
0,
ri!excelData.Amount_1
)
)
)
)

))

test data

-----------

{{Name: "Ptrick",
Month: "JULY" ,
Amount_1: "$98",
Amount_2: "$987",

},
{Name: "Ptrick",
Month: "JULY" ,
Amount_1: "$18"},{Name: "jhgf",
Month: "JULY" ,
Amount_1: "$170",
Amount_2: "$987",

},
{Name: "jhgf",
Month: "JULY" ,
Amount_1: "$90"

}}

output should groupby name