Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Add rows that is derived from another row based on certain condition in each row. Please help.

Hi, Need help. I imported two -3 sheets and they have like 5 columns. Now based on values in column#5, I want to add a new column in which each row will have a certain condition. Eg. for below tab...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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 10

     

    If 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 10

     

    Implement 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

  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi BA_Pete 

 

Is it possible to make the response non case sensitive? like in my above response check for 'yes', can we allow it to read the value regardless which way it is written like it accepts all options - yes, Yes or YES.

Thanks in advance.

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi Anonymous ,

 

I think you've got two options here:

 

1) Use Comparer.OrdinalIgnoreCase. You would probably implement this by swapping out your straight text evaluations for Text.Contains, something like this:

// From this:
if [Question ID] = "AD-01" and [Answers] = "Yes" then 1

// To this:
if [Question ID] = "AD-01" and Text.Contains([Answers], "Yes", Comparer.OrdinalIgnoreCase)

 

2) Right at the start of your query step list, select the [Answers] column, then go to Transform tab > Format > UPPERCASE (or lowercase). Just make sure the evaluation values match whichever case you choose in your IF code.

 

Option 1 is tidier (less steps).

Option 2 is simpler.

 

Pete