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{}
}