Forum Discussion
Add rows that is derived from another row based on certain condition in each row. Please help.
Hi Anonymous ,
Apologies if I've misunderstood your requirement, but I think you need to code your column something like this:
if [Query] = "Please answer ques1" and [Response] = "Yes" then 1 else if [Query] = "Please answer ques1" and [Response] = "No" then 999 else if [Query] = "Ques2" and [Response] = "Yes" then 5 else if [Query] = "Ques2" and [Response] = "No" then 0 ... else //your escape action e.g. null, 0 etc.Pete
Hi Anonymous ,
I'm assuming that the user can answer with numeric values for DO-05?
If so, then you just need to use regular operators to evaluate them:
else if [Question ID] = "DO-05" and [Answers] < 20 then 20 else if [Question ID] = "DO-05" and [Answers] < 40 then 10If they can answer with numeric values, but these are being converted to text in your collection system, then you'd need to do something like this:
else if [Question ID] = "DO-05" and Number.From([Answers]) < 20 then 20 else if [Question ID] = "DO-05" and Number.From([Answers]) < 40 then 10Implement one of these changes for all of your "<X" lines and it should work (the "Azure" and "UPN" lines are fine, they're just not getting evaluated due to the errors in previous condition lines.
Pete
Hi Anonymous ,
You should be able to make your first condition a catch-all on the [Answers] column then continue your conditions as they are:
if [Answers] = null then 0 ... else if [Question ID] = "DO-05" and Number.From([Answers]) < 20 then 20 else if [Question ID] = "DO-05" and Number.From([Answers]) < 40 then 10 ... ... else //escape valuePete
Hi BA_Pete ,
on the above range score, my query is breaking because there are rows that have not received response and have 'null' as the value. now that we converted it expects a numeric value, it starts throwing error on those rows and all following rows. I tried checking for null and then in secnd entry converted them to numeric but it didn't help. example -
else if [QuestionID] = "DO-05" and [Answers] = "null" then 0
else if [QuestionID] = "DO-05" and Number.From([Answers]) <20 then 20
else if [Question ID] = "DO-05" and Number.From([Answers]) < 40 then 10
Please help.
Hi Anonymous ,
You should be able to make your first condition a catch-all on the [Answers] column then continue your conditions as they are:
if [Answers] = null then 0
...
else if [Question ID] = "DO-05" and Number.From([Answers]) < 20 then 20
else if [Question ID] = "DO-05" and Number.From([Answers]) < 40 then 10
...
...
else //escape value
Pete