Using SUPP QNAM in VLM WhereClause for parent ORRES variable

Using SUPP QNAM in VLM WhereClause for parent ORRES variable

Interesting!!!
But I don't think it is correct, depending on what you want to achieve:
SUPPxx is an artificial construct just because reviewers cannot distinguish between standard variables and non-standard variables (NSVs). Neither can their tools as these are not "SDTM savvy" at all.
Essentially, in your case "MYVAR" is the variable, which unfortunately needs to be "banned" to SUPPLB (I suppose) for the above reasons. So, if this "SUPPxx banning" would not be mandated, it just is a normal variable in LB (I suppose). So, if it were not "banned" there would just be a "MYVAL" column in LB.
As WhereClause-s are always about variable-value pairs, your "WhereClause" would be something like (in "human" language):

WHERE LBTESTCD=VITB12 AND LBSPEC=SERUM and MYVAR=my_value

WIth "my_value" is what is a value of QVAL for QNAM=MYVAR can be.

P.S. I haven't seen a rule yet for define.xml that states something about how real the variable names and variable values in WhereClauses need to be, at least not in the CDISC rules. Some vendors may however have implemented such.
Interesting in this view is that CDISC CORE will allow users to develop their own (e.g. QA) rules, and just add them to the local copy of the validation engine. I know at least one CRO who is already offering that to their customers.

Lex

on January 7, 2025

SUPP QNAM in a WhereClause

I see the use case, and I would think it is allowed. I think QVAL should be part of the WhereClause, not QNAM. In the WhereClause the reference to an ItemDef should be referencing the ItemDef that defines the MYVAR non-standard variable in the Value Level Metadata.

Non-standard variables can be part of the key variables. At the same token they can be part of a WhereClause. The only issue is that there is no metadata that tells me that this variable is not part of the LB dataset, but instead the SUPPLB dataset. I think you should add a comment to the WhereClause explains the situation. In that sense there is some similarity with an example from the Define-XML 2.1 specification where the COUNTRY variable from the DM domain is used in a WhereClause for VS.

It is the question however whether applications writing or reading a Define-XML document will support these constructs.

Btw, unless it is a specific P21 Define-XML question, I would post this in the LinkedIn Define-XML group (https://www.linkedin.com/groups/4975366/).

SUPP QNAM in a WhereClause

As in the XML itself we use OIDs, something like: ???

<def:WhereClauseDef OID="WC.MYVAR">
  <RangeCheck Comparator="EQ" ... def:ItemOID="IT.SUPPLB.QNAM>
    <CheckValue>MYVAR</CheckValue>
  </RangeCheck>
  <RangeCheck Comparator="EQ" ... def:ItemOID="IT.SUPPLB.QVAL>
    <CheckValue>my_value</CheckValue>
  </RangeCheck>
  <!-- and further selections for LBTESTCD and LBSPEC  -->
</def:WhereClauseDef>

I must think a bit about this ... especially about whether the combination of the two above "RangeCheck"s in combination with the ones for LBTESTCD and LBSPEC does indeed correctly do the selection (I first need more coffee so early in the morning ...).

@Colm: As indeed this has little to do with P21, can you, as Lex proposed, copy/move this discussion to the LinkedIn Define-XML group (https://www.linkedin.com/groups/4975366/).
It surely is also very interesting for people who struggle with similar questions.

Thanks Jozef, Lex....it is…

Thanks Jozef, Lex....it is quite an interesting thing I must say as even using Pinnacle 21 Community to build the define.xml, it seems to understand that when you click the QNAM in the WhereClause of LB that it belongs to SUPPLB (see address url in latest attachment) and when i click it it brings be straight to the QNAM's of SUPPLB.

@Lex I am not sure if putting QVAL in the WhereClause would be good, as technically the QVAL could be anything, or at least potentially dozens of options. The value matters less than the QNAM for example in our use case unless I have misunderstood ?

SUPP QNAM in a WhereClause

So you mean "select the records for which there is a supplemental qualifier variable QNAM=MYVAR with any possible value" (e.g. a float)? That's of course also a case ...
P.S. If the P21 tool (based on Excel) does not enable these cases, you can of course always use another (modern) tool, or edit/adapt the result define.xml file manually after it has been created.
That's one of the things that we teach during the CDISC-Define-XML courses ...
Disadvantage of this approach is that when then something else changes, you will need to edit the result define.xml again ... :-(
This is mostly not the case with other (GUI-based) tools.

Lex

on January 8, 2025

SUPP QNAM in a WhereClause

Hi Colm,

A single condition in a WhereClause has 2 parts: 

• Which variable are you restriction
• What are the values you are restricting to

For the first part I wrote "In the WhereClause the reference to an ItemDef should be referencing the ItemDef that defines the MYVAR non-standard variable in the Value Level Metadata."

This assumes that you have Value Level Metadata for SUPPLB, that defines an ItemDef for QNAM=MYVAR

      <def:ValueListDef OID="VL.SUPPLB.QVAL">
        <ItemRef ItemOID="IT.SUPPLB.QVAL.MYVAR" OrderNumber="1" Mandatory="No">
          <def:WhereClauseRef WhereClauseOID="WC.SUPPLB.QNAM.MYVAR"/>
        </ItemRef>
      </def:ValueListDef>

      <def:WhereClauseDef OID="WC.SUPPLB.QNAM.MYVAR">
        <RangeCheck Comparator="EQ" SoftHard="Soft" def:ItemOID="IT.SUPPLB.QNAM.MYVAR">
          <CheckValue>MYVAR</CheckValue>
        </RangeCheck>
      </def:WhereClauseDef>

      <ItemDef OID="IT.SUPPLB.QVAL.MYVAR" Name="MYVAR" DataType="text" Length="10" SASFieldName="MYVAR">
        <Description>
          <TranslatedText xml:lang="en">Description of MYVAR</TranslatedText>
        </Description>
        <def:Origin Type="Collected" Source="Vendor">
      </ItemDef>

So now to create you WhereClause for LBORRES, you can do:

      <def:ValueListDef OID="VL.LB.LBORRES">
        <ItemRef ItemOID="IT.LB.LBORRES.SET1" OrderNumber="1" Mandatory="No">
          <def:WhereClauseRef WhereClauseOID="WC.LB.LBTESTCD.SET1"/>
        </ItemRef>
      </def:ValueListDef>

      <def:WhereClauseDef OID="WC.LB.LBTESTCD.SET1">
        <RangeCheck Comparator="EQ" SoftHard="Soft" def:ItemOID="IT.LB.LBTESTCD">
          <CheckValue>VITB12</CheckValue>
        </RangeCheck>
        <RangeCheck Comparator="EQ" SoftHard="Soft" def:ItemOID="IT.LB.LBSPEC">
          <CheckValue>SERUM</CheckValue>
        </RangeCheck>
        <RangeCheck Comparator="EQ" SoftHard="Soft" def:ItemOID="IT.SUPPLB.QVAL.MYVAR">
          <CheckValue>MYVARVALUE1</CheckValue>
        </RangeCheck>
      </def:WhereClauseDef>

      <ItemDef OID="IT.LB.LBORRES.SET1" Name="MYVAR" DataType="text" Length="10">
        <Description>
          <TranslatedText xml:lang="en">Description of </TranslatedText>
        </Description>
        <def:Origin Type="Collected" Source="Vendor">
      </ItemDef>

I have not fully tested this yet, but I think this should be the idea. I need to see what the stylesheet does when I make a complete example.