Select record in SV for which e.g. LB.VISITNUM = SV.VISITNUM

[JozefAerts]

For Findings datasets, I need to retrieve the record from SV (Subject Visits) with the same VISITNUM as in my Findings dataset for my subject.
E.g. LB.VISITNUM = SV.VISITNUM,
So that we can then compare e.g. LB.LBDTC with SV.STDTC.
I am trying to do so with an Operation.
Sofar I have:
Check:
all:
# From within our Findings dataset,
# we need to look up the visit in SV, i.e. LB.VISITNUM = SV.VISITNUM
# Starting from the Findings dataset:
- name: VISITNUM
operator: exists
- name: VISITNUM
operator: non_empty
- name: VISITNUM
operator: equal_to
value: $sv_visitnum
and:
Operations:

• domain: SV
  id: $sv_visitnum
  name: VISITNUM
  operator: distinct

I do however that the operation returns a List/Array, so that "equal_to" is not appropiate. ANYWAY, it must exactly match the value in LB ...

Can anyone provide me a suggestion?

[pendingintent]

I passed your request through a model I use for another project because I was interested in what I would get as a response. What do you think?

A well-known pattern in the CDISC Rules Engine, and it avoids the array problem entirely by using group on a grouping operation (min_date/max_date/min/max) instead of distinct.

Group by both USUBJID and VISITNUM, so the engine computes the value per group and then left-merges it back onto your Findings dataset row-by-row on those same key columns.

That merge is the LB.VISITNUM = SV.VISITNUM join. Each LB record already gets its own matching SV value attached.

Distinct doesn't work for this because it returns a set per group even when grouped. min_date/max_date return the actual date scalar per group instead.

Since SV should have exactly one record per USUBJID+VISITNUM, min_date or max_date both just return that single date:

Check:
  all:
    - name: VISITNUM
      operator: exists
    - name: VISITNUM
      operator: non_empty
    - name: LBDTC
      operator: date_less_than       # or date_greater_than, whichever direction you're flagging
      value: $sv_svstdtc
Operations:
  - operator: min_date
    domain: SV
    name: SVSTDTC
    id: $sv_svstdtc
    group:
      - USUBJID
      - VISITNUM

Drop your equal_to $sv_visitnum condition and the separate distinct Operation entirely — the group: [USUBJID, VISITNUM] on the min_date operation will do the join for you.

NOTE: it's a left merge, so a Findings record whose VISITNUM has no matching SV record will get a null $sv_svstdtc — decide whether you want/need an extra exists/non_empty check on $sv_svstdtc before the date comparison, depending on how your rule should treat unmatched visits.

[JozefAerts]

Thanks @pendingintent ! I will give it a try.
Just wondering whether that will also correctly treat e.g. VISITNUM = 2 when there is also already a VISITNUM = 1 for the same subject.
Yes, I am aware of the possible issue that there is no record in SV for the given VISITNUM. Unfortunately, that is something we see regularly.

[JozefAerts]

Great! It works now!
And that just before taking a long weekend ... :-)

[pendingintent]

Excellent! I hope that helps you enjoy your long weekend!