web stats
Transformer mappings from a spreadsheet? - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 01-23-2014, 07:42 AM
AaronJM AaronJM is offline
What's HL7?
 
Join Date: Jan 2014
Posts: 5
AaronJM is on a distinguished road
Question Transformer mappings from a spreadsheet?

I'm using Mirth Connect v3.0.1.7051 for Windows 7.

I have two XML files with ID numbers in them, where I want to map an ID in the input file to an ID in the output file.

So this file:
Code:
<in>
    <id>2</id>
    <id>5</id>
    <id>11</id>
<in>
...would be transformed to this file:
Code:
<out>
    <id>9</id>
    <id>14</id>
    <id>23</id>
<out>
This is easy enough with regular mapping steps ("out[0] <-- in[0]", "out[1] <-- in[1]", etc.). There are going to be thousands of IDs per file though, and it should be easy for non-developers to create new ID mappings.

Is it possible for Mirth to import mappings from a spreadsheet, either CSV or Excel? Maintaining a spreadsheet, where there'd be two columns to match the input and output IDs, would be easier than going into Mirth and editing mapping steps by hand.
Reply With Quote
  #2  
Old 02-03-2014, 09:05 AM
AaronJM AaronJM is offline
What's HL7?
 
Join Date: Jan 2014
Posts: 5
AaronJM is on a distinguished road
Question Creating mappings between XML formats representing lists

I'm trying to map two XML formats representing lists. For example, if my input XML looks like this:
Code:
<in>
    <a>1</a>
    <a>2</a>
    <a>3</a>
    ...
</in>
I'd want my output XML to look like this:
Code:
<out>
    <b>450</b>
    <b>600</b>
    <b>785</b>
    ....
</out>
Where the value of 1 gets mapped to the value of 450, 2 gets mapped to 600, etc. And it is mapped into a differently named XML element in the output XML. There could be one, many or no values within the <in> element.

The transformation editor only seems to let me make mappings between fixed static nodes though. How do I do mappings for XML formats representing lists of things?

In my case, the XML I am working with represents lists of drugs. I'd have a list of multiple drugs given to a patient that is using our internal code IDs. I want to have a transformation that will use a global mapping(?) where I specify our internal code that is associated with a specific code and have it iterate through all those looking for a match and when it finds when it creates the resulting XML output. All I've managed to do so far is create a global mapping by loading a CSV file through JavaScript (is there a better way though?).
Reply With Quote
  #3  
Old 02-03-2014, 09:25 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,123
narupley is on a distinguished road
Default

You can do that with E4X. For example, just create an outbound XML template of <out/>, and then manually append nodes:

Code:
var node = <{nodeName}>{nodeValue}</{nodeName}>;
tmp.appendChild(node);
In that code, nodeName and nodeValue are variables that you populate beforehand, in whatever way you want (selecting from a database, iterating through a properties file, etc.).

I don't understand where or how that data is being stored, so I don't know what would be the best way of iterating through it. If you can elaborate on that more, maybe I could give some suggestions.

More info on E4X here: http://wso2.com/project/mashup/0.2/d...uickstart.html
__________________
Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

Nicholas Rupley
Work: 949-237-6069
Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


- How do I foo?
- You just bar.

Last edited by narupley; 02-03-2014 at 09:28 AM.
Reply With Quote
  #4  
Old 02-04-2014, 03:43 AM
AaronJM AaronJM is offline
What's HL7?
 
Join Date: Jan 2014
Posts: 5
AaronJM is on a distinguished road
Post

Thanks very much for the prompt reply. I apologize if my first posting wasn't clear so I'll attempt to be more specific.

The intent is to have an XML file come in as an input to a web service in the channel. There will then be an output file in XML that will go to a different destination web service.

We want to be able to have non-developers create mapping documents that are like a key-value pair for these large mapping instances. The key would be the source ID for a field option value and the value would be the target ID for a field option value.

As an example, in our product the Drug XML element (let's say it is <Drugs>) may have a source ID of 15 for Morphine and a source ID of 20 for Aspirin. In the target system they have a Drug XML element (let's say it is <Treatments>) where we want to map source of 15 to target of 4000 and source of 20 to 5000. So we would have a file that would have two lines in it (or could be JSON formatted or something else)
15, 4000
20, 5000

Right now I created a global mapping by loading in a CSV file through the channel's Deploy script. So I am storing the mapping data inside a dictionary in globalChannelMap, where the key is the source ID and the value is the target ID. If you have any thoughts or suggestions on better ways to store the mapping files/data or access them that would be greatly appreciated.

Our actual implementation will be that the incoming message is our custom XML and the outgoing message will be an HL7 V3 message. So does the node creation and appendChild suggestion you have work OK in that situation or are there special methods for writing into HL7 V3 from Javascript?

Last edited by AaronJM; 02-04-2014 at 03:53 AM. Reason: Grammar
Reply With Quote
  #5  
Old 02-04-2014, 06:44 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,123
narupley is on a distinguished road
Default

Gotcha. Well, loading from a properties file is certainly a valid method. So is loading from a database, which you can do either once on deploy, or on-the-fly.

If users change those mappings and then expect new messages coming into the interface to use those new mappings immediately without any other intervention, then it would make sense to update that information on-the-fly for every message. That can be done either with a properties file or with a database table.

If on the other hand you intentionally don't want users' changes to be updated in real time without intervention from a channel engineer who would have to redeploy the channel, then it makes more sense to load them into memory once on deploy, and use them thereafter.

If using a properties file, how will the users update that file? Do you have a GUI with which to do that, or are they expected to manually edit a CSV (probably a bad idea)? If using a database to store that information, you would certainly need some sort of user interface for it, unless all your users are SQL savvy or something (and even then, KISS). In either case, do you have a backup plan? Assuming that wherever you're storing that data (be it in a flat file or in a database) is on some clustered NAS then it's probably okay either way, but that's something to consider.

There are other options as well. Perhaps the mappings are stored on a completely separate machine, and instead of having Mirth Connect talk straight to that external database, you put an instance of Mirth Connect on that machine as well, and then prop up a SOAP or RESTful web service with a well-defined interface (like some JSON or something). In this case, you've now abstracted the communication/transport layer from the data retrieval layer, and you can swap out the latter layer from a flat file to a database table without affecting the HTTP interface at all.

So really, it all depends.
__________________
Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

Nicholas Rupley
Work: 949-237-6069
Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


- How do I foo?
- You just bar.
Reply With Quote
  #6  
Old 02-04-2014, 06:46 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,123
narupley is on a distinguished road
Default

Quote:
Originally Posted by AaronJM View Post
Our actual implementation will be that the incoming message is our custom XML and the outgoing message will be an HL7 V3 message. So does the node creation and appendChild suggestion you have work OK in that situation or are there special methods for writing into HL7 V3 from Javascript?
It won't be the exact same code because obviously HL7v3 has a more complex structure than the XML you posted. But yes, that can all be done rather easily. As noted before you can find a useful primer on E4X here: http://wso2.com/project/mashup/0.2/d...uickstart.html
__________________
Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

Nicholas Rupley
Work: 949-237-6069
Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


- How do I foo?
- You just bar.
Reply With Quote
  #7  
Old 05-07-2014, 12:39 AM
lordc001 lordc001 is offline
What's HL7?
 
Join Date: Apr 2014
Posts: 5
lordc001 is on a distinguished road
Default

I am new to Mirth and javascript, and i am trying to load csv file to the globalmap or global channel map. Is there a way you could post the javascript code you used in the Deploy tab???
Reply With Quote
  #8  
Old 05-08-2014, 09:07 AM
AaronJM AaronJM is offline
What's HL7?
 
Join Date: Jan 2014
Posts: 5
AaronJM is on a distinguished road
Default

I ended up using Apache POI to import Excel spreadsheets. I said I was using CSV files, but they start out as Excel files and Apache POI allows me to skip converting them to CSV.

Reading my Excel files looks like this:
Code:
var captionID = msg.FIELD.@ID.toString()

var mappingFile = new Packages.java.io.FileInputStream(new Packages.java.io.File('C:\\mappings.xlsx'))
var mappingsBook = new Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook(mappingFile)
var mappings = mappingsBook.getSheetAt(0)

var rowIt = mappings.iterator()
while (rowIt.hasNext())
{
	var row = rowIt.next()

	var cellCaptionID = row.getCell(1).getNumericCellValue().toString()
	if (captionID.equals(cellCaptionID))
	{
		typeIdentifier = row.getCell(3).getStringCellValue()
		code = row.getCell(2).getNumericCellValue().toString()
		break;
	}
}

tmp['CODE'] = code
Here I'm not populating any global map. Instead I'm reading my Excel file to transform a field from my input format before writing it to a file in my output format.
Reply With Quote
  #9  
Old 09-01-2019, 06:26 PM
vijayk333 vijayk333 is offline
Mirth Newb
 
Join Date: Jul 2014
Posts: 25
vijayk333 is on a distinguished road
Default

Hi Aaron,

Do you have example of channel and excel file? It would be great if you post channel.

Thank you in advance.

Vijay
Reply With Quote
Reply

Tags
channel, mapping, transform

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 06:30 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation