This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Combine datasets from different stored procedures into one output

Go to solution
Max
New Contributor II

‎01-25-2024 07:45 AM - edited ‎03-06-2024 11:34 AM

Hello,

I have a pipeline that uses two different stored procedures. The issue I'm having is that I need to incorporate the output of the second stored procedure (ranges) into every object of the first stored procedure output (risks).

The output of the first stored procedure(risks) is:

[
{
"SupplierCode":"GVN00000007",
"SupplierTeamLeadCode":"Top 150",
"SupplierLocationCode":"UNITED STATES",
"SupplierLocationStateCode":"Georgia",
"SupplierCategoryCode":"Metals",
"DBRisk":"8",
"OwnershipRisk":10.0,
"RestrictionsRisk":10.0,
"ContractRisk":15.0,
"SourceRisk":8.0,
"GeopoliticalRisk":10.0,
"CapacityRisk":15.0,
"ContributingRisk":0.0,
"FailedMarketRisk":10.0,
"AggregateRisk":86.0,
"RiskLevel":"Low Risk",
"Mitigation_Code":null
},
{
"SupplierCode":"GVN00000541",
"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"
},
{
"SupplierCode":"GVN00000541",
"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"
},
{
"SupplierCode":"GVN00045254",
"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"
}
]

Output of second stored procedure and mapper - (ranges)

[
{
"ranges":{
"highRiskLimitRange":"0.00-75.00"
"mediumRiskLimitRange":"75.10-85.00"
"lowRiskLimitRange":"85.10-1000.00"
}
}
]
 
The expected result is:
 
{
"risks": [
{
"riskDetails": {
"supplierCode": "GVN00000007",
"supplierLocationCode": "UNITED STATES",
"supplierLocationStateCode": "Georgia",
"dbRisk": "8",
"ownershipRisk": 10.0,
"restrictionsRisk": 10.0,
"contractRisk": 15.0,
"sourceRisk": 8.0,
"geopoliticalRisk": 10.0,
"capacityRisk": 15.0,
"contributingRisk": 0.0,
"failedMarketRisk": 10.0
},
"aggregateRiskDetails": {
"aggregateRisk": 86.0,
"riskLevel": "Low Risk",
"ranges": {
"highRiskLimitRange": "0-75",
"mediumRiskLimitRange": "75.10-85",
"lowRiskLimitRange": "85.10-1000"
}
},
"mitigationStrategies": {
"mitigationCodes": []
}
},
{
"riskDetails": {
"supplierCode": "GVN00000541",
"supplierLocationCode": "UNITED STATES",
"supplierLocationStateCode": "Minnesota",
"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
},
"aggregateRiskDetails": {
"aggregateRisk": 73.5,
"riskLevel": "High Risk",
"ranges": {
"highRiskLimitRange": "0-75",
"mediumRiskLimitRange": "75.10-85",
"lowRiskLimitRange": "85.10-1000"
}
},
"mitigationStrategies": {
"mitigationCodes": [
"Implementing a contract",
"Increased Inventory"
]
}
},
{
"riskDetails": {
"supplierCode": "GVN00045254",
"supplierLocationCode": "AUSTRALIA",
"supplierLocationStateCode": "No_State_Code",
"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
},
"aggregateRiskDetails": {
"aggregateRisk": 75.0,
"riskLevel": "High Risk",
"ranges": {
"highRiskLimitRange": "0-75",
"mediumRiskLimitRange": "75.10-85",
"lowRiskLimitRange": "85.10-1000"
}
},
"mitigationStrategies": {
"mitigationCodes": [
"Risk Accepted by Business"
]
}
}
]
}

@koryknick- this post has been out there for a while and I was wondering if you might be able to have a look. Your help is always appreciated!

Thank you,

Max

 

0 Kudos
1 ACCEPTED SOLUTION

Go to solution
koryknick
Employee
Employee

‎03-06-2024 11:42 AM

@Max - what you're asking for is a Cartesian Product between the first and second outputs - basically every record from one path combined with every record from the other.  You can do this using a Join snap configured as follows:

koryknick_0-1709754105666.png

Hope this helps!

View solution in original post

2 REPLIES 2

Go to solution
koryknick
Employee
Employee

‎03-06-2024 11:42 AM

@Max - what you're asking for is a Cartesian Product between the first and second outputs - basically every record from one path combined with every record from the other.  You can do this using a Join snap configured as follows:

koryknick_0-1709754105666.png

Hope this helps!

Go to solution
Max
New Contributor II
In response to koryknick

‎03-06-2024 12:32 PM - edited ‎03-06-2024 12:33 PM

@koryknick- Works perfectly, Thank you so much!!

0 Kudos