How to pull all rows between two distinct rows into new file

Microsoft Windows xp professional w/serv...
November 1, 2016 at 13:30:15
Specs: Windows XP
First and foremost I want to apologize for ANYONE who attempts this. This is not an easy undertaking and I am willing to pursue other avenues of code (Powershell or VBScript). I also digress but tried to put in all the info I think is needed to capture the output I need. And lastly - THANK YOU in advance for your help, if any at all.

1. I have an HL7 flat file that has pipe-delimited rows with varying number of pipes per row (depending on the row). Each PID row can have 1 or more subsequent OBR segments, each OBX can have 1 or more rows related to the OBR, only 1 NTE and FT1 rows per OBR, and it will continue until the next PID row.
2. I need to find a distinct row starts with "PID" and log a few of the pipe delimited rows into a variable.
3. Then I need to pull in the subsequent related rows OBR, OBX, NTE, and FT1 rows and associate them with the parent PID until the next PID shows up, and continue this recurse until EOF.

EXAMPLE file (with one distinct PID and the subsequent rows associated):
PID|2||0061334374^^^^MR~ABC1234ZZ^^^^HC~123456789^^^^SS||MEMBERLNAME3^MEMBERFNAME3||19YY0MMDD|M|||1307 ADDRESS^^SANTA PAULA^CA^93060^^H||(805) 000-0000
OBR|1||EN464842K^QD^LAM|7500011293^FECAL GLOBIN BY IMMUNOCHEM. (MEDICARE)^L|||20141114000000-0600|||||||||NPI-1285671206^MEYER^ALEXANDER||||76088107\S\MEDGRP MEDICAL GROUP|VLC\S\IPA 1|20141120175500-0600|||C
OBX|1|ST|5778-6^COLOR^LN^30005500^COLOR^L||YELLOW|STDNULL99^STDNULL99^ISO+|YELLOW||||F
OBX|2|ST|5767-9^APPEARANCE^LN^30005600^APPEARANCE^L||CLEAR|STDNULL99^STDNULL99^ISO+|CLEAR||||F
OBX|3|NM|5811-5^SPECIFIC GRAVITY^LN^30006000^SPECIFIC GRAVITY^L||1.024|STDNULL99^STDNULL99^ISO+|1.001-1.035||||F
OBX|4|NM|5803-2^PH^LN^30006200^PH^L||6.0|STDNULL99^STDNULL99^ISO+|5.0-8.0||||F
NTE|||Not Detected
FT1|1|||20150106003758-0600||NA|7500011293^FECAL GLOBIN BY IMMUNOCHEM. (MEDICARE)^L|||||||^^DMHC|||||V7651^STDNULL99^I9C
OBR|2||EN464842K^QD^LAM|9700000200^TEST AUTHORIZATION^L|||20141114000000-0600|||||||||NPI-1285671206^MEYER^ALEXANDER||||76088107\S\MEDGRP MEDICAL GROUP|VLC\S\IPA 1|20141120175500-0600|||C
OBX|1|ST|19145-2^TEST(S) ORDERED ON REQUISITION^LN^97005000^TEST(S) ORDERED ON REQUISITION^L||FECAL GLOBIN BY|STDNULL99^STDNULL99^ISO+|STDNULL99||||C
OBX|2|ST|30896-5^TEST CODE:^LN^97007000^TEST CODE:^L||11293X|STDNULL99^STDNULL99^ISO+|STDNULL99||||C
OBX|3|ST|97006000^CLIENT CONTACT:^L||JUNE M|STDNULL99^STDNULL99^ISO+|STDNULL99||||C
OBX|4|ST|97008000^REPORT ALWAYS MESSAGE SIGNATURE^L||RAM|STDNULL99^STDNULL99^ISO+|STDNULL99||||C
NTE|||~The laboratory testing on this patient was verbally requested~or confirmed by the ordering physician or his or her authorized~representative after contact with an employee of Quest Diagnostics.~Federal regulations require that we maintain on file written~authorization for all laboratory testing. Accordingly we are asking~that the ordering physician or his or her authorized representative~sign a copy of this report and promptly return it to the client~service representative.~~~Signature:____________________________________________________~~~Fax number: (213) 785-1750~


The tree structure is as follows
>PID (1 per set)
>>OBR (1 or more per PID)
>>>OBX (1 or more per OBR)
>>>>NTE (only 1 per OBR)
>>FT1 (only 1 per OBR)

And I need to capture what is in PID field 2 & 3 (ie "2" & "0061334374^^^^MR~ABC1234ZZ^^^^HC~123456789^^^^S") on each row and then each OBR + OBX fields. When multiple OBRs occur per PID I would create a new row with the same PID info, but the new OBR info. Then for each OBX I would have the PID + OBR rows with the same info and the new OBX fields.

I've been able to find the PID segment and extract what I want to a new pipe delimited file. I can also find all the OBR and OBX segments individually, but I am having a hard time recursing through rows for each PID, then subsequent OBR, etc. I am just trying to figure the best way to concatenate the data on a row.

Desired output would be something like this....(with header)

PID|PIDUID|PIDPTID|PIDPTNAME|PIDPTDOB|OBRID|OBRDESCRIPTION|OBRDOS|OBXID|OBXRESULT|NTENOTE|FT1ID
PID|2|006130460^^^^MR|MEMBERLNAME2^MEMBERFNAME2|19YY0MMDD|1|7500011293^FECAL GLOBIN BY IMMUNOCHEM. (MEDICARE)^L|20141114000000-0600|1|5778-6^COLOR^LN^30005500^COLOR^L|Not Detected|20150106003758-0600
PID|2|006130460^^^^MR|MEMBERLNAME2^MEMBERFNAME2|19YY0MMDD|1|7500011293^FECAL GLOBIN BY IMMUNOCHEM. (MEDICARE)^L|20141114000000-0600|2|5767-9^APPEARANCE^LN^30005600^APPEARANCE^L|Not Detected|20150106003758-0600
PID|2|006130460^^^^MR|MEMBERLNAME2^MEMBERFNAME2|19YY0MMDD|1|7500011293^FECAL GLOBIN BY IMMUNOCHEM. (MEDICARE)^L|20141114000000-0600|3|5811-5^SPECIFIC GRAVITY^LN^30006000^SPECIFIC GRAVITY^L|Not Detected|20150106003758-0600
PID|2|006130460^^^^MR|MEMBERLNAME2^MEMBERFNAME2|19YY0MMDD|1|7500011293^FECAL GLOBIN BY IMMUNOCHEM. (MEDICARE)^L|20141114000000-0600|4|5803-2^PH^LN^30006200^PH^L|Not Detected|20150106003758-0600


@echo off
setlocal enabledelayedexpansion enableextensions

REM --------------------------------- LOCATION OF FILES (possibly go through full dir) ---------------------------------REM
PUSHD "E:\Users\ME"

SET loc=%CD%
ECHO !loc!

REM --------------------------------- SETTING ORIGINAL FILENAME PIECES FOR FINAL RENAME ---------------------------------REM
SET origname=%%a

REM --------------------------------- LOOPING THROUGH THE DIR AND PARSING OUT THE FILE NAME INTO PIECES ---------------------------------REM
FOR /F "tokens=* delims=" %%a IN ('DIR /b/a-d') DO (
ECHO filename:%%a

REM --------------------------------- RETURN ALL MESSAGE SEGMENTS ---------------------------------REM
FOR /F "tokens=* delims=" %%A IN ('findstr "PID" !origname!') DO (
SET "work=%%A"
REM fill empty fields with #NUL#
REM but do it multiple times just in case consecutive fields are empty
REM ECHO !work!
FOR /L %%i IN (1,1,3) DO SET "work=!work:||=|#NUL#|!"
REM ECHO !work!
FOR /F "tokens=2,4,6,8 delims=|" %%B IN ("!work!") DO SET UID=%%B&& SET PtID=%%C&& SET PtName=%%D&& SET PtDOB=%%E
REM ECHO PID %%A
>>"E:\Users\ME\ParsePID.TXT" ECHO "!origname!|!UID!|!PtID!|!PtName!|!PtDOB!"

REM FOR /F "tokens=* delims=" %%o IN ('findstr "OBR" !origname!') DO ECHO %%o

REM FOR /F "tokens=* delims=" %%x IN ('findstr "OBX" !origname!') DO ECHO %%x
)

message edited by tcerveny


See More: How to pull all rows between two distinct rows into new file

Report •


#1
November 1, 2016 at 20:56:50
So much depends on your ultimate objective. The output file structure should be tailored to your objective, which I am going to assume is a fast lookup of the PID field values to return data? I doubt you will need to duplicate all that data shown in the output file sample, but I don't know yet. Sorry for the "non-solution", but it's better (for me) to know what you're aiming at to help build an optimal solution. Sorry for this "non-solution", but I want to make the result work best for you.

Report •

#2
November 1, 2016 at 21:04:29
Nbrane, no worries. I tried my best to elaborate, but alas it wasn't good enough....so here goes. Ultimately, I need to associate the PID values in field 2 and 3 down to each level related to that one PID. Which means I need to reference (PID) for each row to act as a primary key/identifier. The same would be when I have an OBR parent with OBX children, I would need to know those children belonged to a parent, and then the OBR belonged to the top level PID. So the PID is parent, OBR are children to PID, and there could be many OBR, OBX are children to OBR, and there could be many OBXs.

Each row in my output file has to be the same number of pipes with the appropriate data for each PID. However, I'm willing to find another solution to do this.

message edited by tcerveny


Report •

#3
November 2, 2016 at 12:02:01
Ok, so here is my attempt using VBScript now, since it may actually be the better approach. Again, I will attempt any Windows native language (BAT, PS1, VBS (also one I am familiar with)).

This just captures each portion of the line and breaks it down into the parts with delimiters. I am now going to use this to build a row, hopefully! #fingerscrossed

Option Explicit

Dim objFSO, colFiles, objFile, Lines, msgSeg, msgField, msgSubField, msgRep, msg
Dim obJYourFolder

objYourFolder = "C:\Users\Desktop\TEST" Rem **********Change To equal path where Files are at*********

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set colFiles = objFSO.GetFolder(objYourFolder).Files
For Each objFile in colFiles
	Wscript.Echo objFile
	Lines = objFSO.OpenTextFile(objFile).ReadAll
		For Each msg in Split(Lines,vbNewLine)
			If msg <> "" then
			msgSeg = (Left(msg,3))
				For Each msgField in Split(msg,"|")
					If instr(msgField,"^") > 0 then
						msgSubField=(Replace(msgField,"^"," "))
							If instr(msgSubField,"~") > 0 then
								For Each msgRep in Split(msgSubField,"~")
									
								Next	
							End If
								
					End If
				Next
			End If
		Next
Next



Report •

Related Solutions

#4
November 2, 2016 at 19:41:59
Ok, you've made and excellent description and what you want to do. I think I've got a handle, but the only thing is what software or method is being used to extract information. My take is you're building a 2-dimensional key structure to access an indefinite number of sub-arrays. My proposal, using the command-line only, for access, and vbscript for creation and maintenance:
Build a file of primary keys with their data, each key+delimited data on one line (PID). The line number is the filename of a file containing the second set of OBR keys with their delimited data. The line number in the this file is the filename of a third file that contains the OBX items. Since the NTE and FT1 are on the same level as the OBR, and only one per OBR, I would simply include them on the OBR master line of data, perhaps delimited or tagged to set them off. For example:
Master index file of PIDs (I'm leaving the pipes, altho I'd rather use something else):
2||0061334374^^^^MR~ABC1234ZZ^^^^HC~123456789^^^^SS||...
Say, for example, this is the sixth line in the PID master, then file "6" would contain a similar list of keys+data for all the OBRs. The line number of the matching OBR key would then be the key to a third set of files, sequentially numbered, of the OBX set related to the parent OBR. If the OBR line is the third, then this set could be named 6-3, pointing you to the third set of OBR, 6th line of PID. The filename itself gives you the ancestry, or reverse-lineage: 3 to parent (filename), 6 to grandparent (line number in PID master). But this whole arrangement is based on using "find" or "findstr" for locating data using batch. If you think this will work, I'll work on it.
Here's my analysis of your example:

EXAMPLE file (with one distinct PID and the subsequent rows associated):

PID|2||0061334374^^^^MR~ABC1234ZZ^^^^HC~123456789^^^^SS||...
PID | key1 | null | key2 | ten more fields - some null

   OBR|1||EN464842K^QD^LAM|7500011293^FECAL GLOBIN BY ...
   OBR | seq# |  null | keyA? | 22 more fields, many null

     OBX|1|ST|5778-6^COLOR^LN^30005500^COLOR^L||YELLOW|...
     OBX | seq# | 11 more fields
     OBX|2|ST|...
   
   NTE|||Not Detected
   FT1|1|||20150106003758-0600||NA|7500011293^FECAL GLOBIN...

message edited by nbrane


Report •

#5
November 2, 2016 at 19:57:27
Yes, your design concept sounds like it could work. I'm trying to get the data tied to a single PID however I can. This will ultimately be loaded to a single database table via a CLI mysql data infile ingestion. Your approach with the multiple files seems like it could work, with some iterative evolution. I'm trying different approaches of course, which I'll post back as soon as I can. Again, thank you!

Report •

#6
November 2, 2016 at 20:13:30
Ah, that was my other question: what "machinery" are you trying to accommodate. My SQL is zero to none or I would pose a more complete solve. There should be a way to "link" subordinate data structures to a hierarchy of keys within the database, since that's a fairly common requirement, without having to replicate the entire subordinate structure for each key and sub-key. Same idea, but better machinery (SQL). Meanwhile good luck!

Report •

#7
November 3, 2016 at 12:04:17
The problem is that in any RDBMS your table structure is fixed; meaning if I want to take a row of the aforesaid output I need to ensure that columns are all the same (even if NULL or empty string values occur). This is why having multiple rows (with varying field counts for PID, OBR, OBX, etc) tie to ONE (or many) set of unique identifiers is challenging.

Report •

#8
November 3, 2016 at 19:05:19
Ok, I'm flaunting my ignorance here! Can a table element be a table unto itself but with keys linking to parent table? That's the gist of what you need, from my understanding. If not, then you may have a massive duplication of data (never good). Also, meant to ask but forgot, how many items we talking here, at each level? You could conceivably have a hundred million top levels, and if each had ten thousand sublevels... You get the idea. With no set boundaries you would have to have a dynamic structure. With set boundaries, you could have a static structure, but it might be very large, which isn't a problem of disk-space so much as efficiency, speed of retrieval, and flexibility. I apologize in advance for my ignorance of this platform. Usually the software can be designed to accommodate sub-structures by linking to another file. But if you don't have source-code and are limited to "off-the-shelf" package, then you have to work with what you've got.

Report •

#9
November 4, 2016 at 10:05:22
So, nbrane -- I took your concept and ran with it. A table element cannot be a table unto itself. However, with joins and temp tables I will be able to make magic happen. I now have created 3 distinct files for my testing. 1 PID, 1 OBR, and 1 OBX output file for a single raw file. I have then passed the line numbers to each row since the files are always created with the PID being the start of a sequence and continuing downward until EOF.

Once I get these files created I can upload them to a database (since each type - PID, OBR, OBX all have the same number of columns and will all have their own table) and start my SQL magic. Essentially I will then look for the top level PID x Line number and traverse through the other tables OBR, then OBX, and when I get to the next PID (in sequence) I have a full data set for that top level PID, and then repeat. And no need to apologize, it was your concept that I started to run with.

Maybe even take the three output files and try to build it back into one file even, but that may be phase two.

Here is my final BAT code.

@echo off
setlocal enabledelayedexpansion enableextensions

CD "C:\Users\Desktop\TEST"

SET loc=%CD%

FOR /F "tokens=* delims=" %%a IN ('DIR /b/a-d') DO (
	ECHO filename:%%a
	
SET origname=%%a
	
REM --------------------------------- RETURN ALL MESSAGE SEGMENTS ---------------------------------REM
FOR /F "tokens=* delims=" %%A IN ('findstr /n "PID" !origname!') DO (
SET "work1=%%A"
REM fill empty fields with #NUL#
REM but do it multiple times just in case consecutive fields are empty
FOR /L %%i IN (1,1,3) DO SET "work1=!work1:||=|#NUL#|!"
FOR /F "tokens=1 delims=: " %%J IN ("%%A") DO SET ln=%%J 
	FOR /F "tokens=2,4,6,8 delims=|" %%B IN ("!work1!") DO SET UID=%%B&& SET PtID=%%C&& SET PtName=%%D&& SET PtDOB=%%E
>>"C:\Users\Desktop\!origname!_ParsePID.TXT" ECHO "!origname!|!ln!|!UID!|!PtID!|!PtName!|!PtDOB!"
)
FOR /F "tokens=* delims=" %%X IN ('findstr /n "OBR" !origname!') DO (
SET "work2=%%X"
REM fill empty fields with #NUL#
REM but do it multiple times just in case consecutive fields are empty
FOR /L %%j IN (1,1,3) DO SET "work2=!work2:||=|#NUL#|!"
FOR /F "tokens=1 delims=: " %%J IN ("%%X") DO SET ln=%%J 
	FOR /F "tokens=2,5,8,17 delims=|" %%Q IN ("!work2!") DO SET OUID=%%Q&& SET Order=%%R&& SET DOS=%%S&& SET Prov=%%T
>>"C:\Users\Desktop\!origname!_ParseOBR.TXT" ECHO "!origname!|!ln!|!OUID!|!Order!|!DOS!|!Prov!"
)
FOR /F "tokens=* delims=" %%M IN ('findstr /n "OBX" !origname!') DO (
SET "work3=%%M"
REM fill empty fields with #NUL#
REM but do it multiple times just in case consecutive fields are empty
FOR /L %%k IN (1,1,3) DO SET "work3=!work3:||=|#NUL#|!"
FOR /F "tokens=1 delims=: " %%P IN ("%%M") DO SET ln=%%P 
	FOR /F "tokens=2,4,6,7,8,9 delims=|" %%F IN ("!work3!") DO SET XUID=%%F&& SET Order=%%G&& SET Value=%%H&& SET Units=%%I&& SET Range=%%J&& Set AbFlag=%%K
>>"C:\Users\Desktop\!origname!_ParseOBX.TXT" ECHO "!origname!|!ln!|!XUID!|!Order!|!Value!|!Units!|!Range!|!AbFlag!"
)
)


Report •

#10
November 5, 2016 at 19:34:10
Yeah, that's about what I was aiming at, but I assumed one "base" file, and you have multiple base files (ie: adding one level to the structure), and you are using original file line numbers instead of extracted line no.s.. Either way works as long as it's consistent. To illustrate: Find "PID"<orig > work1, subsequent: FIND /n "pidstring" work1 gives a serial line#, different from: find /n "PID"<orig which gives a "random" line# due to indefinite number of sub-lines under the given PID. The null-field-fill (good work there) could be a subroutine, but that's just window-dressing and more trouble that it's worth. Good luck, keep posting

Report •

#11
November 7, 2016 at 15:43:57
*Looks at input* ... *Looks at sample output*

Is it just PID row fields 1, 2, 4, 6, and 8,
plus OBR row fields 2, 5, and 8,
plus OBX row fields 2, and 4,
plus NTE row field 4,
plus FT1 row field 5?

How To Ask Questions The Smart Way

message edited by Razor2.3


Report •

#12
November 7, 2016 at 19:20:23
Lmao @ link. And no, the tokens are listed in each for/f call in my latest.

2,4,6,7,8,9 for OBX
2,5,8,17 for OBR
2,4,6,8 for PID

I ignored the FT1 and NTE segments to make my life easier.


Report •

#13
November 7, 2016 at 20:43:02
Link? Oh, the signature block. Yeah, that used to be sized smaller and was more separated from the actual post, but at some point it wasn't and I never got it looking right.

So, those fields. Which order should they be? You can include the NTE and FT1 rows too, because I need to keep track of them to know when to spit out a record. Also, which version of Windows are you going to run this script on? You said you were okay with PowerShell, but your listed OS is XP, and I'm not going to bother with a .ps1 script if you don't at least have access to v2.

How To Ask Questions The Smart Way


Report •

#14
November 7, 2016 at 20:58:35
Ohh, maybe the reason the boards defaulted me to that XP is bc when I joined in 09 that's what it was set to. Sorry about that. It will be windows 7 64bit and windows 2012 server.

The order of the fields are as listed. In sequence... PID, then OBR, then OBX... Toss in a NTE and then an FT1.

message edited by tcerveny


Report •

#15
November 8, 2016 at 06:20:18
Powershell script, before coffee, and sans error checking:
[cmdletbinding()]param(
  [string]$inFile = "in.txt",
  [string]$outFile = "out.txt"
)

'PID|PIDUID|PIDPTID|PIDPTNAME|PIDPTDOB|OBRID|OBRDESCRIPTION|OBRDOS|OBXID|OBXRESULT|NTENOTE|FT1ID' |
 Out-File $outFile -Force

Get-Content $inFile |
 ForEach-Object -Begin { $outLine = ""; $pidInfo = "" } {
  $line = $_ -split '\|'
  switch -Exact ($line[0]) {
    "PID" { $pidInfo = $line[1,3,5,7] -join '|' }
    "OBR" { $outLine = $pidInfo + $($line[1,4,7,16] -join '|') }
    "OBX" { $outLine += '|' + $($line[1,3,5,6,7,8] -join '|') }
    "NTE" { $outLine += '|' + $line[3]  }
    "FT1" { $outLine += '|' + $line[4]; $outLine }
  }
 } |
 Out-File $outFile -Append

It's easier to think about it as a record spread across 3 lines, with a superset that needs to be added.

How To Ask Questions The Smart Way

message edited by Razor2.3


Report •

#16
November 8, 2016 at 08:03:20
Razor2.3 - I tested this and it looks very close to what I need. I see where the lack of coffee may have caused a missed pipe (after the last token of the PID segment), but that is beyond trivial.This is good....great as a matter of fact. However, I need to see how I can load into a DB table and that is where the complication comes in. They do not have the same number of fields, and I would need to repeat (like a tree) the duplicate/redundant portion of the rows so I could load into a table. I was attempting powershell, but had massive array complications (on the part of me just not knowing the correct angle to attack).

This was my attempt...

# Modify the path variable
$path = "C:\Users\Desktop\TEST"
$src1 = (Get-ChildItem $path).FullName
$src = (Get-Content $src1)
# take hl7 message as string, convert to  obj


    # go from hl7 to array of objects
    $msg = @()
    $cursorCount = 0
    $src | Foreach {
        $split = $_.Split("|")
        $split2 = $_.Split("^")
        $split3 = $_.Split("~")
        
        $nodeName = $split[0]
        
        $properties = @()
        $split | select -skip 1 | foreach {    
            $properties += $_
        }
        
        $object = New-Object psobject
        $object | Add-Member –membertype noteproperty –Name HL7Pos –Value $cursorCount
        $object | Add-Member –membertype noteproperty –Name Segment –Value $nodeName
        $object | Add-Member –membertype noteproperty –Name Fields –Value $properties
        
        $msg += $object
        
        #select $properties.Split("^") 
        #$object | Select {$_.Segment -eq 'PID'} #true/false
        #Write-Host $object[0] #@{HL7Pos=102; Segment=NTE; Fields=System.Object[]}
        #Write-Host $properties[0] #2nd field (^~\& and 1,2,3,4,etc)
        #Write-Host $properties[1] #3rd field
        #Write-Host $properties[2] #4th field PID 3.1
        -join $nodeName + ' '+ $properties[0,2] #"PID 1 11880^^^^PT~XEH900570586^^^^MR"
        $cursorCount += 1

    }
 

message edited by tcerveny


Report •

#17
November 9, 2016 at 07:59:13
tcerveny: I need to see how I can load into a DB table
You can tell me this if you want, but I'm only going to retort that it sounds like your DB needs to be normalized.

tcerveny: They do not have the same number of fields, and I would need to repeat (like a tree) the duplicate/redundant portion of the rows so I could load into a table.
Not sure what you're getting at. You're going to need to expand on this a lot. Start with defining what you mean by "they."

How To Ask Questions The Smart Way


Report •

#18
November 9, 2016 at 09:43:44
ok - simpler approach - using different raw data set

PARENT|1|FNAME|LNAME|DOB|F
CHILD|1|VISIT1|HOSPITAL|THIRDFL|LABS|0800|20160101
SUBCHILD|1|BLOODDRAW|1|201601010800
CHILD|2|VISIT2|MDOFFICE|SECONDFL|LABS|0800|20160101
SUBCHILD|1|XRAY|1|201601010900
SUBCHILD|2|INJECTION|1|20160101000

Output should be something like this

PARENT|FNAME|LNAME|DOB|CHILD|1|VISIT1|HOSPITAL|THIRDFL|SUBCHILD|1|BLOODDRAW
PARENT|FNAME|LNAME|DOB|CHILD|2|VISIT2|MDOFFICE|SECONDFL|SUBCHILD|1|XRAY
PARENT|FNAME|LNAME|DOB|CHILD|2|VISIT2|MDOFFICE|SECONDFL|SUBCHILD|2|INJECTION

All columns for the output are the same and roll up to the PARENT (first position of the row), then next comes the CHILD portion, and then the SUBCHILD portion. Redundancy exists, but all columns are the same and can be loaded into a single table and show every SUBCHILD all the way back to the PARENT entry of the "tree".

message edited by tcerveny


Report •

#19
November 10, 2016 at 05:59:59
I'm getting the feeling you've been working on this problem for so long, you're losing your ability to communicate the issue to the uninitiated. Let's take a step back and keep it simple.

What fields in what records can be null?
What do you do when a null field is encountered?
What data is redundant?
Should we grab the missing data from another record of the same type?
If so, is the first record guaranteed to be complete?

How To Ask Questions The Smart Way

message edited by Razor2.3


Report •

Ask Question