0

I don't if I'm being a knucklehead, but I've searched considerably and tried several options: can't make it happen. Here is the issue.

I have the following Data Query: enter image description here

First of all, sorry it's French. Secondly, as you see, the fields are "File number", the "Year" and the "Number of distinct patients".

Wanted result: I want to filter for Case numbers who appear both at 2015 and 2016.

As you see, certain patients showed up several years in a row. However, I want to find out how many showed up in 2015 AND 2016 only (not 2014, etc.) (so a total of 2 visits for the 2 years combined). So solely filtering in my Data Query on 2015 and 2016, doesn't do the job, since it fails to exclude the patients that only showed up once. Furthermore, filtering on 2015 and 2016, and then Count(Fields!File_number.Value)=2 wouldn't work since it fails to exclude the patients that might have been here in 2014.

I have tried several Boolean expressions, but as soon as I include 2 years in my filter, it blanks my tablix out. (Understandibly cause I tell it that Fields!Year.Value must be equal to 2015 and 2016 simultaneously).

So I tried bypassing it by telling it that and did the following: Expression:

=IIF(Lookup(Fields!FileNumber.Value, Fields!FileNumber.Value, Fields!Year.Value, "Dataset")=2015 AND Lookup(Fields!FileNumber.Value, Fields!FileNumber.Value, Fields!Year.Value, "Dataset")=2016, True, False)

It works for 1 year only: as soon as I add 2015 either by and "AND" or by adding an additionnal filter, mytable goes blank.

Any suggestions? Thanks!

Bob
  • 467
  • 1
  • 4
  • 13

1 Answers1

1

Yeah, this isn't an easy thing to solve, you aren't being a knucklehead :) You are correct about the double boolean expressions in the query and the two combined lookups won't work either. In the case of a lookup, LOOKUP returns the first match in a dataset, so every time, both of those looksups are going to return the first value.

There is another function called LookupSet which does return ALL matches from a query though. It should be possible to create an ugly expression around this function to test specifically whether the string 2015 and 2016 both appear in the results with something like:

=Join(LookupSet(Fields!FileNumber.Value, Fields!FileNumber.Value, Fields!Year.Value, "Dataset"),",")

and then substring searching for both "2015" and "2016" (I'll let you fiddle with that part)

Daniel
  • 1,364
  • 1
  • 19
  • 34
  • Thanks Daniel for confirming that the issue isn't solely between my two ears =') and thanks for the answer! It works perfectly with the Expression field containing your answer, "In" operator and [2016,2015] as Values. Just to push this an extra step, could you suggest, how to formulate the Value field if I want 2014 and 2017? Ex.: [2017,|*|,|*|,2014]? – Bob Jun 29 '17 at 16:21
  • You are asking for to supply two dates (I assume as dynamic parameters) and then search anything in between? – Daniel Jun 29 '17 at 16:46
  • Finally, I realised that the context for my question didn't make sense. I was simply wondering (out of curiousity) how to use a wildcard with a situation like this. Since the Join(LookupSet(...) produces a string, I wanted to know if there's a way to use the * symbol in some way to compensate for the caracters that would be missing. But don't bother, it was pure curiosity and no added-value to my situation. Thanks a lot! – Bob Jun 29 '17 at 17:14
  • Well, I believe there is no wildcard syntax that would solve this particular situation. However, if the min/max years were coming from a dynamic parameter source, I would suggest looking at custom code-behind functions. It would be trivial to loop between two integers and create the value array with a VB function – Daniel Jun 29 '17 at 17:20
  • I see. It's a bit more of a hefty process. Thanks! – Bob Jun 29 '17 at 17:45