QA Multi Source Query Uses Cartesian Products

Questions and Answers

---

Q&A - Multi-Source Query Uses Cartesian Products

Question

Device IPC in definition of "DeviceVTEProphylaxis" passes as expected, but it does not pass through definition of "VTEProphylaxis". It makes sense knowing "from" statement generates Cartesian products of all sources, but I would like to know a clear specification how to implement in CQL. I re-wrote the statement as 2 sets of encounters and it still does not work.

Answer

My read of the updated VTEProphylaxis is, roughly, that there exists a "MedicationVTEProphylaxis" or a "DeviceVTEProphylaxis" 1 day or less after the start of the encounter, or 1 day or less after the end of an anesthesia procedure that ends 1 day or less after the start of the encounter.

What this means then is that the "from" will return results only in the case that there are both "MedicationVTEProphylaxis" and "DeviceVTEProphylaxis". It would better reflect the intent to split this into different expressions:

  define "Encounters with Medication VTE Prophylaxis":
    "Initial Population" E
      with "MedicationVTEProphylaxis" M
        such that M.relevantPeriod starts 1 day or less on or after day of start of E.relevantPeriod
          and M.relevantPeriod starts on or after start of E.relevantPeriod

  define "Encounters with Device VTE Prophylaxis":
    "Initial Population" E
      with "DeviceVTEProphylaxis" D
        such that D.relevantPeriod starts 1 day or less on or after day of start of E.relevantPeriod
          and D.relevantPeriod starts on or after start of E.relevantPeriod

  define "Encounters with Anasthesia and Medication VTE Prophylaxis":
    from
      "Initial Population" E,
      ["Procedure, Performed": "General or Neuraxial Anesthesia"] P,
      "MedicationVTEProphylaxis" M
    where P.relevantPeriod ends 1 day or less on or after day of start of E.relevantPeriod
      and P.relevantPeriod ends on or after start of E.relevantPeriod
      and M.relevantPeriod starts 1 day or less on or after day of start of E.relevantPeriod
      and M.relevantPeriod starts on or after start of E.relevantPeriod

  define "Encounters with Anasthesia and Device VTE Prophylaxis":
    from
      "Initial Population" E,
      ["Procedure, Performed": "General or Neuraxial Anesthesia"] P,
      "DeviceVTEProphylaxis" D
    where P.relevantPeriod ends 1 day or less on or after day of start of E.relevantPeriod
      and P.relevantPeriod ends on or after start of E.relevantPeriod
      and D.relevantPeriod starts 1 day or less on or after day of start of E.relevantPeriod
      and D.relevantPeriod starts on or after start of E.relevantPeriod

  define "Encounters with VTE Prophylaxis":
    "Encounters with Medication VTE Prophylaxis"
    union
    "Encounters with Device VTE Prophylaxis"
    union
    "Encounters with Anesthesia and Medication VTE Prophylaxis"
    union
    "Encounters with Anasthesia and Device VTE Prophylaxis"

Question

After reading your code, I'd like to know if there is any difference between yours and mine. In high-level, the structure differences are...

My codes are:

  E,M,D --> (M∩E) and (D∩E)
  P,M,D --> (M∩P) and (D∩P) *** P has been already defined as procedure in Encounter ***

Your codes are:

  E,M   --> M∩E
  E,D   --> D∩E
  E,P,M --> (P∩E) and (M∩E) *** P is a Data Type only ***
  E,P,D --> (P∩E) and (D∩E)

So, I do not see any reason to produce different outcomes. For testing purpose, I will revise my code to follow your coding structure. I will let you know if it works or not.

Also, the other reason I report this issue is that both "Initial Population" and "DeviceVTEProphylaxis" are passing as individual definition. But only "Initial Population" turns green in "VTE Prophylaxis" definition, but not "DeviceVTEProphylaxis".

I am applying SQL inner join concept here.

Answer

A multi-source query is a cartesian product, so it's not appropriate to describe it as an intersect, it's more appropriate to say:

  E, M, D --> E x M x D
  P, M, D --> P x M x D

And the problem with this formulation is that since what we're looking for is VTE Prophylaxis via either a Medication or a Device, a cartesian product will not produce what we're after. Instead, what I'm suggesting is:

  E, M --> E x M
  E, D --> E x D
  P, M --> P x M
  P, D --> P x D

And then unioning the results of all four of these:

  (E x M) U (E x D) U (P x M) U (P x D)

Which brings up another difference between what was originally written and what I'm suggesting, which is that in the proposed representation, the Medication or Device Prophylaxis for Procedures occurs with respect to the Procedure's relevantPeriod, not the Encounter's relevantPeriod.