Answered

Optimise way of working to calculate a score by comparing arrays

(0) Share

Report

Posted on by CH-22072116-0

Hi Power Automate guru's,

I'm wondering if someone has an optimise solution avoiding if possible embedded Apply to each loops to extract scores from an array based on another array containing the scores.

Array 1, contains all the answered questions:

{
 "body": [
 {
 "questionId": "r06430a48b48b48fe8f559ff62efbc92c",
 "questionType": "Question.Choice",
 "questionChoiceType": 2,
 "responseValue": [
 "Yes - Public ",
 "Yes - Private "
 ]
 },
 {
 "questionId": "r0e59e70ad94a41d69f9e0f71c636c89f",
 "questionType": "Question.Choice",
 "questionChoiceType": 1,
 "responseValue": "Yes"
 },
 {
 "questionId": "r102096f9a4a44032b386bef9da7cdd5d",
 "questionType": "Question.Choice",
 "questionChoiceType": 1,
 "responseValue": "Reactive"
 }
 ]
}

We can identify 2 type of questions:

questionChoiceType 1 => single answer stored as string in responseValue
questionChoiceType 2 => multi answers stored as array of selected responses

Then I have my array 2 containing all the possible answers for every QuestionID and corresponding score:

{
 "body": [
 {
 "QuestionID": "r0e59e70ad94a41d69f9e0f71c636c89f",
 "Choice": "Yes",
 "Score": 3
 },
 {
 "QuestionID": "r0e59e70ad94a41d69f9e0f71c636c89f",
 "Choice": "No",
 "Score": 2
 },
 {
 "QuestionID": "r102096f9a4a44032b386bef9da7cdd5d",
 "Choice": "No",
 "Score": 3
 },
 {
 "QuestionID": "r102096f9a4a44032b386bef9da7cdd5d",
 "Choice": "Reactive",
 "Score": 2
 },
 {
 "QuestionID": "r102096f9a4a44032b386bef9da7cdd5d",
 "Choice": "Preventive",
 "Score": 3
 },
 {
 "QuestionID": "r06430a48b48b48fe8f559ff62efbc92c",
 "Choice": "No",
 "Score": 3
 },
 {
 "QuestionID": "r06430a48b48b48fe8f559ff62efbc92c",
 "Choice": "Yes - Public",
 "Score": 4
 },
 {
 "QuestionID": "r06430a48b48b48fe8f559ff62efbc92c",
 "Choice": "Yes - Private",
 "Score": 2
 }
 ]
}

What I would love to obtain as a result:

=> Adding a score property to array 1 with the obtained score

Score calculation depends on the type of questionChoiceType

questionChoiceType 1 => straight forward, if responseValue string = Choice string the you get the score
questionChoiceType 2 => every element of the array gives a score. Your final score is the sum of the different multi select selected responses. (in my sample 4 + 2)

{
 "body": [
 {
 "questionId": "r06430a48b48b48fe8f559ff62efbc92c",
 "questionType": "Question.Choice",
 "questionChoiceType": 2,
 "responseValue": [
 "Yes - Public ",
 "Yes - Private "
 ],
 "score": 6
 },
 {
 "questionId": "r0e59e70ad94a41d69f9e0f71c636c89f",
 "questionType": "Question.Choice",
 "questionChoiceType": 1,
 "responseValue": "Yes",
 "score": 3
 },
 {
 "questionId": "r102096f9a4a44032b386bef9da7cdd5d",
 "questionType": "Question.Choice",
 "questionChoiceType": 1,
 "responseValue": "Reactive",
 "score": 2
 }
 ]
}

I tested different methods by every time I face new challenges 😉 I might be to tired of need another vision to point me in the right direction.

I was thinking about first using a filter to isolate only the qestionChoiceType 1 and use Damo's tutorial to generate an object "questionID_responseValue", then select where array 1 "questionID_responseValue" = array 2 "questionID_responseValue"

But then I'm stuck with the questionChoiceType 2 that I first need to split to use the same principle....

Maybe I over complicate things ;-p, but it's because of performance and the high number of questions/answers, raison why I want to avoid as much as possible using nested apply to each.

Any help, advice, sample, solution is more than welcome to obtain the score in an optimise way.

Thank you very much in advance

Chris

Categories:

General topics

All responses (2)

Answers (1)

Sort by

Verified answer

v-xiaochen-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @ChrisBI ,

First, the 'responseValue' value from first array is not equals to the 'Choice' value from second array.

We can find that there are extra spaces at the end of the string in the first image.

In my test, I've removed the extra spaces. This is a different point.

@and(equals(item()?['Choice'], items('Apply_to_each')?['responseValue']),equals(item()?['QuestionID'], items('Apply_to_each')?['questionId']))

{
"root": {
"Numbers": @{body('Select')}
}
}

xpath(xml(outputs('Compose')), 'sum(/root/Numbers)')

addProperty(items('Apply_to_each'),'score',outputs('Compose_2'))

@and(contains(items('Apply_to_each')?['responseValue'], item()?['Choice']),equals(item()?['QuestionID'], items('Apply_to_each')?['questionId']))

xpath(xml(outputs('Compose_3')), 'sum(/root/Numbers)')

addProperty(items('Apply_to_each'),'score',outputs('Compose_4'))

Result-

[

  {

    "questionId": "r06430a48b48b48fe8f559ff62efbc92c",

    "questionType": "Question.Choice",

    "questionChoiceType": 2,

    "responseValue": [

      "Yes - Public",

      "Yes - Private"

    ],

    "score": 6

  },

  {

    "questionId": "r0e59e70ad94a41d69f9e0f71c636c89f",

    "questionType": "Question.Choice",

    "questionChoiceType": 1,

    "responseValue": "Yes",

    "score": 3

  },

  {

    "questionId": "r102096f9a4a44032b386bef9da7cdd5d",

    "questionType": "Question.Choice",

    "questionChoiceType": 1,

    "responseValue": "Reactive",

    "score": 2

  }

]

Best Regards,

Wearsky
CH-22072116-0 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-xiaochen-msft

Thank you very much Wearsky ! Not the first time you're helping me out, really appreciate all your time, energy and effort to help newbies like me.

Perfect solution... I was close but you came up with a solution to address all the remaining challenges I faced.

Thanks you

Take care
Chris