0

I have the below data in a dataset called Questions and all IDs in another dataset called Dataset1

ID   Answer
1    Yes
2    Yes
2    No
2    Yes
3    No

My expected output should be as below

ID    Yes    No
1     1      0
2     2      1
3     0      1

I am trying to match the ids from Dataset1 and get the Answer from Questions dataset. If I just use Lookup, it is just checking the first match and ignoring the second record. For eg, in the above data, for ID-2, it is checking the first record with id 2 and counting 'Yes' and ignoring the other 'No' and 'Yes'

=Sum(iif(Lookup(Fields!ID.Value, Fields!ID.Value, Fields!answer.Value, "Questions") = "Yes", 1, 0))

I want to count all Yes and No like shown in the expected output above

I have tried using Lookupset but I couldn't get it working. Is there any easier way without using custom code. If custom code is necessary, could you please advise on how to achieve this. Thank you in advance.

sparc
  • 345
  • 1
  • 2
  • 13

2 Answers2

0

Why do you need a second dataset?

Have a matrix on the questions dataset, row grouped by ID then use a sum in each column as follows:

=SUM(IIF(Fields!answer.Value="Yes",1,0))
=SUM(IIF(Fields!answer.Value="No",1,0))
RET
  • 861
  • 6
  • 15
  • Hi, thanks for your response. Dataset1 is the main dataset and Questions dataset has only the ones which has answers. Not all of them had their answers. Of the IDs in Dataset1, I need to show which ones had answers. So, I need Dataset1 and match the IDs from Questions dataset. Could you please let me know how do I achieve this. Thank you, – sparc Nov 12 '18 at 08:58
0

You can count the output from a lookupset by using the following custom code:

Function CountAnswers(ByVal AnswerArray As Object(),   Answer As string) As Object

If AnswerArray Is Nothing Then Return cint(0)
If Answer Is Nothing Then Return "Nothing"

Dim TotalCount As Decimal = New Decimal() 
Dim AnswerPosition As Decimal = New Decimal()

TotalCount = 0 
For AnswerPosition = 0 to AnswerArray.GetUpperBound(0)
    TotalCount  = Switch(IsNothing(AnswerArray(AnswerPosition)),TotalCount, cstr(AnswerArray(AnswerPosition))=Answer,TotalCount + 1, TRUE,TotalCount)
Next

return TotalCount

End Function

Then enter the following under "Yes" and "No" on a matrix on Dataset1:

Code.CountAnswers(Lookupset(Fields!ID.Value, Fields!ID.Value, Fields!answer.Value,"Questions"),"Yes")
Code.CountAnswers(Lookupset(Fields!ID.Value, Fields!ID.Value, Fields!answer.Value,"Questions"),"No")
RET
  • 861
  • 6
  • 15