Convert Mulsoft dataweave code to SnapLogic

[Max]

Hello, I have the following MuleSoft code im trying to convert into SnapLogic. I have tried everything i can think of and im not getting anywhere. Also, posted below is a sample output from stored procedure and the expected output i need. Thank you for you time and help!

MuleSoft dataWeave Code:

%dw 2.0
output application/json
var payloadGroupByRiskLevel = ((vars.edpResponse.resultSet1 map ((item, index) -> {
"state": item.SupplierLocationStateCode default "No_State_Code",
"country": item.SupplierLocationCode,
"riskLevel": item.RiskLevel
})) groupBy ((item, index) -> item.riskLevel))
var overallRisk = avg((vars.edpResponse.resultSet1 map ((item, index) -> item.AggregateRisk) default [])) as String {format: "0.00"}
---
{
"riskDetails": {
"riskScore": {
"overAllRisk": overallRisk,
"lowRiskCount": sizeOf(payloadGroupByRiskLevel["Low Risk"] default []),
"mediumRiskCount": sizeOf(payloadGroupByRiskLevel["Medium Risk"] default []),
"highRiskCount": sizeOf(payloadGroupByRiskLevel["High Risk"] default []),
"statesByLowRisk": ((payloadGroupByRiskLevel["Low Risk"].state) distinctBy $) orderBy $,
"statesByMediumRisk": ((payloadGroupByRiskLevel["Medium Risk"].state) distinctBy $) orderBy $,
"statesByHighRisk": ((payloadGroupByRiskLevel["High Risk"].state) distinctBy $) orderBy $,
"countriesByLowRisk": ((payloadGroupByRiskLevel["Low Risk"].country) distinctBy $) orderBy $,
"countriesByMediumRisk": ((payloadGroupByRiskLevel["Medium Risk"].country) distinctBy $) orderBy $,
"countriesByHighRisk": ((payloadGroupByRiskLevel["High Risk"].country) distinctBy $) orderBy $,
}
}
}

Output from stored Prodcedure:

[

{

"SupplierCode":"GVN12345",

"SupplierTeamLeadCode":"Top 150",

"SupplierLocationCode":"UNITED STATES",

"SupplierLocationStateCode":"Minnesota",

"SupplierCategoryCode":"Metals",

"DBRisk":8,

"OwnershipRisk":5.0,

"RestrictionsRisk":10.0,

"ContractRisk":15.0,

"SourceRisk":8.0,

"GeopoliticalRisk":10.0,

"CapacityRisk":7.5,

"ContributingRisk":0.0,

"FailedMarketRisk":10.0,

"AggregateRisk":73.5,

"RiskLevel":"High Risk",

"Mitigation_Code":"Implementing a contract",

"RankNumber":1,

"RowNumber":1,

"TotalResults":4

},

{

"SupplierCode":"GVN12345",

"SupplierTeamLeadCode":"Top 150",

"SupplierLocationCode":"UNITED STATES",

"SupplierLocationStateCode":"Minnesota",

"SupplierCategoryCode":"Metals",

"DBRisk":8,

"OwnershipRisk":5.0,

"RestrictionsRisk":10.0,

"ContractRisk":15.0,

"SourceRisk":8.0,

"GeopoliticalRisk":10.0,

"CapacityRisk":7.5,

"ContributingRisk":0.0,

"FailedMarketRisk":10.0,

"AggregateRisk":73.5,

"RiskLevel":"High Risk",

"Mitigation_Code":"Increased Inventory",

"RankNumber":1,

"RowNumber":2,

"TotalResults":4

},

{

"SupplierCode":"GVN56789",

"SupplierTeamLeadCode":"WCA",

"SupplierLocationCode":"AUSTRALIA",

"SupplierLocationStateCode":null,

"SupplierCategoryCode":"Metals",

"DBRisk":5,

"OwnershipRisk":7.5,

"RestrictionsRisk":10.0,

"ContractRisk":10.0,

"SourceRisk":5.0,

"GeopoliticalRisk":10.0,

"CapacityRisk":7.5,

"ContributingRisk":10.0,

"FailedMarketRisk":10.0,

"AggregateRisk":75.0,

"RiskLevel":"High Risk",

"Mitigation_Code":"Risk Accepted by Business",

"RankNumber":3,

"RowNumber":3,

"TotalResults":4

},

{

"SupplierCode":"GVN11223",

"SupplierTeamLeadCode":"CWS",

"SupplierLocationCode":"SWEDEN",

"SupplierLocationStateCode":null,

"SupplierCategoryCode":"Polymers",

"DBRisk":8,

"OwnershipRisk":10.0,

"RestrictionsRisk":10.0,

"ContractRisk":10.0,

"SourceRisk":8.0,

"GeopoliticalRisk":10.0,

"CapacityRisk":15.0,

"ContributingRisk":10.0,

"FailedMarketRisk":10.0,

"AggregateRisk":91.0,

"RiskLevel":"Low Risk",

"Mitigation_Code":null,

"RankNumber":4,

"RowNumber":4,

"TotalResults":4

}

]

Expected output:

{
"riskDetails": {
  "riskScore": {
    "overAllRisk": “79.83”
    "lowRiskCount": 1,
    "mediumRiskCount": 0,
    "highRiskCount": 2,
    "statesByLowRisk": ["No_State_Code"],
    "statesByMediumRisk": [],
    "statesByHighRisk": ["Minnesota"],
    "countriesByLowRisk": ["SWEDEN"],
    "countriesByMediumRisk": [],
    "countriesByHighRisk": ["AUSTRALIA","UNITED STATES"]
}
}
}

[Max]

@koryknick

Hello, wondering if you might have a look at this issue. I appreciate your help. Thank you!

[acbradley]

Hi Max,

There may be a few more tweaks needed depending on how you would handle any unexpected data, but the below pipeline should give you a good start. You could cut down the number of Snaps in the pipeline with Script Snaps or additional expressions in the Mapper Snaps. 

 

 

[koryknick · Accepted solution]

@Max - as usual, different developers approach a solution differently.  Here is my take on it.

It took me a moment to realize I had to first deduplicate the dataset, which is what the Map for Unique, Sort for Unique, and Unique snaps are doing.  Then it's a pretty simple Aggregate using some conditional logic in the expressions, the the final Mapper to get everything aligned as desired.

Note the use of nested ternary in the "Map final" to handle the No_State_Code default values.  I typically don't prefer to use nested ternary as it gets difficult to interpret pretty quickly... normally I would use the Conditional snap to handle these cases.

Pipeline is attached.

Hope this helps!

[Max]

@koryknickThis is perfect, Thank you!