EDI Source - XML Mapping

[Jeffry Proctor]

Data Source is proprietary EDI text file, ST as header w/batch ID that occurs once per file. Will parse into ~30 tables w/each table record sharing the claim ID, Batch ID, and Import Date. The claim ID is in ~CLM segment, and Batch ID in ~ST.

Each data record has prefix ~N1....~BEN...~CLM... and other mandatory & optional segments & fields

Some Segments have children 1:1 and some are many:many, see pseudo data below.

Question: Where is the ability to share the Claim to all potential ~30 tables for one encounter/record, and Batch ID for all records in the one file?

pseudo data...

ST|110|21545|~BGN|ZZ|ALL CLAIMS EXTRACT|20160101|215614|PT|~N1|93|MEDIMPACT HEALTHCARE SYSTEMS|10680 TREENA STREET|5TH FLOOR|SAN DIEGO|CA|92131|~
NM1|AHF01|PHP-CA MEDD H5852|AHF99999X|01|I|MAHANNING|GREGORIO|X|19490422|66|M|AHF99999X|999999999|1|||D|99999999E|~BEN|0107003|PHP_RW_CA|20060101||10769|R|H5852001RC|1|1|||||R|~CLM|5009832672|D0|233607|P|20151229|20151229|1|APPROVED||||1|20151008|3|01||43513|0|20160101||6547410634|30.000|30|Y|0|||1||||||MPDPOWPRTL|N|||3|0|0||~ND0|0|03|59676056630|PREZISTA||||0|1||N|015574|ASPROD|AHF01||~PRV|01|1245289818|CVS PHARMACY|7021 HOLLYWOOD BLVD||LOS ANGELES|CA|90028-0000|LOS ANGELES|3238360307|3238360311|1542|CVS PHARMACY INC|941059121|0475950558|1|01|1265744999|ZIN|M|OO|1300 N VERMONT AVE|STE 407|LOS ANGELES|CA|90027|3236620492|3236620196||FO4848391|207RI0200X|INFECTIOUS DISEASES|65|INTERNAL MEDICINE|PHYSICIAN|5621936|~CST|STD|1330.88|2.25|0.00|0.00|1482.99|1333.13|1266.47|0.00|0.00|0.00|0.00|0.00|0.00|0.00|0.00|1482.99||||||0.00|~MST|0||66.66|66.66|0.00|0.00|~DRG|1|03|59676056630|PREZISTA|30.000||2|O|PREZISTA 800 MG TABLET|2|33|ANTIVIRALS|W5P|ANTIVIRALS, HIV-SPEC, NON-PEPTIDIC PROTEASE INHIB|08180808|HIV PROTEASE INHIBITORS|F||ORAL|TABLET|800 MG|0|||||Y|TA|~DCT|STD|1330.88|01|AWPE|||~PRD|D||D||C|H5852|001|368509175A|1|~BSA|04|1333.13|~

NM1....

SE...eof

Thanks

JeffP...

[Jeffry Proctor]

Found many issues w/my XML configuration file, here's an improved version that returns data into Multicast w/data viewer on.

I may still have this question, but I'll start a new post/thread When (optimistic?) that occurs...

Thanks,

JeffP...

[Ivan Peev]

Hi Jeffry,

Shouldn't the CLM segment be repeating segment? You should see the parent segment elements appear in the CLM segment output (if repeating).

[Jeffry Proctor]

Ivan, The CLM segment is 1:1 to NM1, better said, NM1:CLM is 1:1, so it repeats within the NM1 leftmost segment ID.

I made the repairs to my XML w/better nesting arrangement and was able to create three tables header, main, and trailer. But I then realized that a couple of segments that loop with maxOccurs="-1" had not been mapped to a multicast, and then I looked at their external & output columns and saw things like NM1, basically repeating the values.

I updated all the looping segments with attribute maxOccurs -1. This is logical that each one can occur so they need their own destination table.

So, Issues in no particular order...

1) The UI was painful to set all the output to str from wstr, I edited the pkg xml to correct, faster find/replace; but is there an option to export non-unicode to avoid having to manually edit many fields, and now duplicated output fields?

2) Why do the NM1 segments repeat under each of the 13 segments now marked w/maxOccurs?

Thanks,

JeffP...

[Ivan Peev]

Hi Jeffry,

1. Unfortunately there is no "quick edit" process. The modification of the XML is the quickest path.

2. If NM1 is parent segment of the repeating segments, the NM1 fields and elements will appear in each and every repeating segment output. We call this segment context and this behavior is by design.

[Jeffry Proctor]

No worries, I messed my first try manually editing... Remaining Issue below...

Attached is the working (not fully tested but saved & build w/out errors) pkg as xml and the xml config files. When I found errors during editing (caused by my dynamic SQL writing the xml config file) like parent reference columns from production that don't exist in the data, I edited the embedded/escaped xml config section w/in the package xml And also the source xml to match.

My edits followed this criteria; 1) remove any un-needed segments w/in output & external sections, retain the claim_id_1 field for all as this is the fk, I'll add the one batch ID from header or trailer after import to SQL. 2) Not done yet, to set all output columns to STR, code set 1252, len 250; again I'll do this in editor as xml.

Issue: There are some optional segments not yet added, but a few are, these are set to minOccurs=0, and those that loop maxOccurs = -1, and the loopers are appearing under the NM1 segment, prefix in raw data and named Claim Member Information. My solution would be to manually edit the xml so these are stand-along segments rather than nested w/in the NM1 segment for if & when data is received that loops --- HOWEVER this brings up a question, (you know there was going to be one...:)

Question - If I have 100 NM1 segments, there are 100 claims, and those segments that can loop will be populated in separate tables w/the fk claim ID and I'll join & pump them into the correct production tables; BUT my co-worker says that in that case, if there were say 2 segments that repeated on one record, there would now be 102 records in the NM1 table, but I disagree and said the other data would be in heaven; there's a dollar riding on this... Please advise.

Thanks,

JeffP...

[Jeffry Proctor]

Well, I'm not sure if I've wasted some time; in the xml I pruned out all what I thought were un-necessary repeated entries retaining only the claim ID and when needed the parent ID. I didn't refresh the xml, but was prompted for smart refresh, hah!

Anyway, I think I can answer my prior question and loose a dollar, if there are two repeating segments in one NM1 line then there are three lines(?)

All of these (see images) appeared after I set the potentially repeating segments maxOccurs = -1, see prior post config xml.

Looking at the xml config, is there a way reduce the number of needed multicast?

[Ivan Peev]

Jeffry,

I'm getting a bit confused. Can you provide more details what do you expect to get as result of the EDI file parsing?

[Jeffry Proctor]

Sorry, there's a few issues, but the main one is that in a perfect world there would be three items on the Inputs Outputs dialog.

Header

Main

Trailer

And as I now understand(?) from a reply you made on another thread, that by design segmenting;

if two EDI records #1 & #2 with one segment repeating for one record are...

NM1~CLM~XYZ~XYZ

NM1~CLM~XYZ

then for rec 1 there are two rows streamed

rec 1 nm1 values1, clm values1, xyz values1

rec 1 nm1 values1, clm values1, xyz values2

rec 2 nm1 values2, clm values2, xyz values2

in SQL qry for distinct in any one segment from Main will return

table NM1

rec 1 values1

rec 2 values1

table CLM

rec 1 values1

rec 2 values1 

table XYZ

rec 1 values1

rec 1 values2

rec 2 values1

And, that's what I had in the first iteration, but as noted in my 2nd or 3rd post, there were some segments that were not included in Main.

Attached is my latest XML, where I removed the minOcuurs=0 and it seems to work w/out that, and more of the segments are now nested under the NM1 segment labeled Claim Member information, and maybe a good time to say that this is a CLAIM record even though that segment CLM appears later in the string and all the other segments attributes of that claim describing the member/patient, drug(s) and coordination of benefits (COB) of who pays what portion and reflect the encounter for conditions like denial.

But, if a segment has maxOccurs = -1 these are showing in their own isolated output, refer to my prior images.

Here is my latest XML where I'm basically trying different patterns to try to get the above ideal result.

I'm going to make a bogus XML file with just the three segments and see if the project will do a refresh and/or another project from scratch, but maybe you can confirm or clarify my miss-conceptions of how to use the tool.

[Ivan Peev]

Jeffry,

You probably have figured the EDI format is similar in terms of the complex hierarchical structures it can represent like the XML format. Therefore it is impossible to represent an EDI input file just as header - main - trailer because it is not that simple.

Let me repeat every repeating (looping) segment is setup to have separate output. The question I have for you is are you trying to relate two separate looping segments and get a joined view which combines the two repeating segments into one view? If that is the case, I would recommend you investigate the special segid attribute definition. When defined , there will be additional column which will contain unique identifier of the current segment . You can use this identifier in combination with the standard merge join transformation to create a common view of two or more repeating segments. Give it a try.

[Jeffry Proctor]

Yes, I have all the correct mapping between EDI & SQL; I'll blame my mentor here who owes me back my dollar... He had me thinking as noted in my prior post that there would be an all in wonder table (main) will all columns assuming one record would have one instance of each EDI field, AND if there was a repeated segment or sub-segment(s) then an additional row would appear in SQL. Then with proper join w/distinct only the relevant net rows for any one segment/SQL table would be returned. Ironic this is something that I have used; and for the naive, think a contact history table with, email, appointments, meetings, different record types and ultimate mappings on the UI tab but all together in one table like soup.

Anyway, back OT, the attached jpg shows my success, so thanks for tolerating my many questions (see below for a couple more), And I know references to the goddess Isis requires reference, but that's what it looks like.

Q: I edited the package via xml, setting the output columns to str from wstr, added code page 1252, but I know from doing this in the past if the EDI Source detects a viable change it will prompt for Smart Refresh which will over-write back to wstr, And consider I don't what to get hour-glass for each of the ~260 fields to transform from wstr to str, it would take hours rather than an hour, Soooo, is there any way to set that as an option and/or to preserve output as str?

Q: Related to above Q, during development, I set undefiedFail=false and removed Optional segments, and assume that if our vendor includes different segments then the package will Error, But what I did was later edit the config xml file changing this to comment, (implies default=true will fail), then edited the package xml to match (section of escaped xml); Sooo, will the EDI Source honor the updated package xml and not want to reset some other code and therefore undo all my manual edits?

Dev: <medi:options undefinedFail="false" />

Prod: <!-- medi:options undefinedFail="false" / -->

Thanks,
JeffP...

[Ivan Peev]

Jeffry,

Let me ask , when you say you have modified the xml changing from DT_WSTR to DT_STR, did you change the type only of the output column ? Or you have also modified the external column definition ?

[Jeffry Proctor]

Yes, used search replace after changing a select few in UI. When I get to office I'll post if it will help.

In the UI, after receiving long hour glass on each change - data type & size/length.

[Ivan Peev]

Jeffry,

Change only the output column metadata. Do not modify the external column metadata. In this way the smart refresh will not change your output column setup.