Find all unique OBX-3/OBR-3 values

Question:
Hi, I am using HL7Spy HL7 SQL for the first time and am trying to pull the values stored in obx-3.1.1 for repeating obx segments but it seems to be only pulling the first value. I thought that this query would accomplish what I needed but I do not believe it is.

SELECT OBx[*]-3.1.1,OBx[*]-3.2.1 WHERE OBx[*]-3.1.1 IS NOT NULL

I would also like to find all unique OBR-4 values too.

Answer:

The HL7 SQL function will only return one result per message, so it probably not what you are looking for.

It would be quite easy to write a Custom Function to do what you are looking for. Is your goal to get a list of OBX-3.1, OBX-3.2 pairs with perhaps a count of how many times each pair occurred? If so, here is some code that will do just that.

private class TrackedItem
{
   public string Key {get; set;}
   public string Value {get;set;}
   public int Counter {get;set;}
}

Dictionary<string,TrackedItem> _knownValues = new Dictionary<string,TrackedItem>(StringComparer.CurrentCultureIgnoreCase);


public override void Run() {
  // Get an HL7 Message in parsed format
  HL7Message message = GetParsedMessage();
  foreach(OBX obx in message.Segments.OfType<OBX>())
  {  
    string key = obx.ObservationIdentifier_03.Identifier_01.Value;   
    TrackedItem item;
    if(!_knownValues.TryGetValue(key,out item))
    {
       item = new TrackedItem {Key=key, Value=obx.ObservationIdentifier_03.Text_02.Value};
       _knownValues.Add(key,item);
    }
    item.Counter = item.Counter + 1;
  }
}

private string _fileName = @"C:\temp\_test.txt";
public override void OnFinish() {
  using(var stream = new System.IO.StreamWriter(_fileName))
  {
    stream.WriteLine("OBX-3.1, OBX-3.2, Count");
    foreach(TrackedItem item in _knownValues.Values.OrderByDescending(i=>i.Counter))
    {
      stream.Write(item.Key);    
      stream.Write("\t");
      stream.Write(item.Value);
      stream.Write("\t");
      stream.WriteLine(item.Counter.ToString());
    }
  }
 
  try{
    System.Diagnostics.Process.Start("Notepad",_fileName);
  } catch{}
}

And, a variation on the same theme for OBR-4

private class TrackedItem
{
   public string Key {get; set;}
   public string Value {get;set;}
   public int Counter {get;set;}
}

Dictionary<string,TrackedItem> _knownValues = new Dictionary<string,TrackedItem>(StringComparer.CurrentCultureIgnoreCase);

public override void Run() {
  // Get an HL7 Message in parsed format
  HL7Message message = GetParsedMessage();
  foreach(OBR obr in message.Segments.OfType<OBR>())
  {  
    string key = obr.universalServiceIdentifier_04.Identifier_01.Value;   
    TrackedItem item;
    if(!_knownValues.TryGetValue(key,out item))
    {
       item = new TrackedItem {Key=key, Value=obr.UniversalServiceIdentifier_04.Text_02.Value};
       _knownValues.Add(key,item);
    }
    item.Counter = item.Counter + 1;
  }
}

private string _fileName = @"C:\temp\_test.txt";
public override void OnFinish() {
  using(var stream = new System.IO.StreamWriter(_fileName))
  {
    stream.WriteLine("OBR-4.1, OBR-4.2, Count");
    foreach(TrackedItem item in _knownValues.Values.OrderByDescending(i=>i.Counter))
    {
      stream.Write(item.Key);    
      stream.Write("\t");
      stream.Write(item.Value);
      stream.Write("\t");
      stream.WriteLine(item.Counter.ToString());
    }
  }

  try{
    System.Diagnostics.Process.Start("Notepad",_fileName);
  } catch{}
}