Skip to Content
avatar image
Former Member

mapping issue

Hi All,

Thanks for all giving good solutions for every issue.

This is very urgent.

i have one mapping issue that i have 5 source fields and 5 target fields to map but in this 5 source fields some fields are getting null values like the below example;

HADR_ST1 = Null

HADR_ST2 = London st

HADR_ST3 = E1W 1S4

HADR_TOWN = Null

HADR_CTY = London

HADR_CTRY = Null

but my requirement in target side is like the below :

Address line 1 = London st

Address line 2 = E1W 1S4

Address line 3 = London

Address line 4 = Null

Address line 5 = Null

Address line 6 = Null

"The mapping is quite complicated:

1- The 1st not empty field of the list: HADR_ST1, HADR_ST2, HADR_ST3, HADR_TOWN, HADR_CTY,

HADR_CTRY must be mapped to Address line 1

2 - The 2nd not empty field of the list: HADR_ST1, HADR_ST2, HADR_ST3, HADR_TOWN, HADR_CTY,

HADR_CTRY must be mapped to Address line 2

3 - The 3rd not empty field of the list: HADR_ST1, HADR_ST2, HADR_ST3, HADR_TOWN, HADR_CTY,

HADR_CTRY must be mapped to Address line 3

4 - The 4th not empty field of the list: HADR_ST1, HADR_ST2, HADR_ST3, HADR_TOWN, HADR_CTY,

HADR_CTRY must be mapped to Address line 4

5 - The 5th not empty field of the list: HADR_ST1, HADR_ST2, HADR_ST3, HADR_TOWN, HADR_CTY,

HADR_CTRY must be mapped to Address line 5

6 - The 6th not empty field of the list: HADR_ST1, HADR_ST2, HADR_ST3, HADR_TOWN, HADR_CTY,

HADR_CTRY must be mapped to Address line 6.

Example:

HADR_ST1 = Null

HADR_ST2 = London st

HADR_ST3 = E1W 1S4

HADR_TOWN = Null

HADR_CTY = London

HADR_CTRY = Null

Address line 1 = London st

Address line 2 = E1W 1S4

Address line 3 = London

Address line 4 = Null

Address line 5 = Null

Address line 6 = Null

Tip: I think it is easier to concatenate all the field in 1 string separated by a special code ( | pipe f.i.) end then use

the substring function to extract the relevant part. "

it is good tip but in this my doubt is that source fields has fixed length but values come variable length so how can we do this issue. Please give me best solution for this.

This is very urgent.

thanks for Advance.

thanks and regards

sai

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

6 Answers

  • avatar image
    Former Member
    Jun 11, 2008 at 07:21 AM

    Hi,

    you should write a UDF for this.

    You pass all input fields and a parameter for the address field index 1 - 6 (so you could use the UDF for all target fields).

    So you will have 7 input parameters:

    HADR_ST1

    HADR_ST2

    HADR_ST3

    HADR_TOWN

    HADR_CTY

    HADR_CTRY

    AddressIndex

    For address field 2 for example you count the not empty fields and map the second one. If such a field not exist you return a null value.

    Regards

    Patrick

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 11, 2008 at 07:26 AM

    Hi Sai,

    Example:

    HADR_ST1 = Null

    HADR_ST2 = London st

    HADR_ST3 = E1W 1S4

    HADR_TOWN = Null

    HADR_CTY = London

    HADR_CTRY = Null

    Address line 1 = London st

    Address line 2 = E1W 1S4

    Address line 3 = London

    Address line 4 = Null

    Address line 5 = Null

    Address line 6 = Null

    i just want known that the source and the target stucture is same as u mentoined in the example

    in the source the null values fileds are randomly placed but were as in target the null value fields are placed at last

    if this is the caser then it can doen by using LOOKUP methods or by UDF

    try to be more clear

    Regards

    Metha

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 11, 2008 at 07:30 AM

    ur method looks to be good but instead of using substring try with a string tokeniser

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 11, 2008 at 08:02 AM

    HI Sai,

    You have right approach about concatenating all fields together.

    and then have to devide it for all the other fields.

    Now here on Source side, take one more temporary element with occurance 0...unbounded.

    It will allow you to create the queue for it.

    So after concatenating you need to sepearate the fields and have to add in this temporary field

    result.addValue() -


    > To Temp Field.

    Then for

    Address line 1 ---> map with Temp[0]

    Address line 2 ---> map with Temp[1]

    Address line 3 ---> map with Temp[2]

    Address line 4 ---> map with Temp[3]

    Address line 5 ---> map with Temp[4]

    PS Note : You have to imply all the logic of udf to all receiver nodes and just have to change the last line as

    return(Temp[0]);

    OR

    return(Temp[1]);

    and so on.......

    Thanks

    Swarup

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 11, 2008 at 09:14 AM

    Hi,

    if you want to use java mapping, here it is some code:

    
    
    		FileInputStream in = null;
    		FileOutputStream out = null;
    		int nullValues = 0;
    		String retValue;
    		try {
    			in = new FileInputStream("C:pathin\\sortmap.xml");
    			out = new FileOutputStream("C:\\pathout\\sortmapout.xml");
    
    //			Create input and output 
    			DocumentBuilder builder = factory.newDocumentBuilder();
    			document = builder.parse(in);
    			docOut = builder.newDocument();
    
    			TransformerFactory tFactory = TransformerFactory.newInstance();
    			Transformer transformer = tFactory.newTransformer();
    			transformer.setOutputProperty(OutputKeys.INDENT, "yes");
    
    			//Create node root
    			Element root = (Element)docOut.createElement("Addresses");
    
    			//append the node root to doc
    			docOut.appendChild(root);	
    
    			//I get all the source nodes 
    			NodeList l = document.getElementsByTagName("HADR").item(0).getChildNodes();
    			for(int j = 0; j < l.getLength();j++){
    				switch(l.item(j).getNodeType()){
    				case Node.ELEMENT_NODE:if(l.item(j).hasChildNodes()){
    					retValue = l.item(j).getFirstChild().getNodeValue();
    					if(retValue.equalsIgnoreCase("null")){
    						nullValues++;
    					}else{
    						Node adrNode = docOut.createElement("Address");
    						adrNode.appendChild(docOut.createTextNode(retValue));
    						root.appendChild(adrNode);
    					}
    				};
    				case Node.TEXT_NODE:
    					String text = l.item(j).getNodeValue();
    					if ((text != null)&& (text.length() == 0 || 
    							text.equals("\n") || text.equals("\\r"))) {
    						break;
    					}
    //					System.out.println("TEXT: " + listaRow.item(i).getNodeValue());
    					break;
    				}
    			}
    
    			//append to the end of the doc. the null nodes
    			for(int i = 0; i < nullValues; i++){
    				Node nullNode = docOut.createElement("Address");
    				nullNode.appendChild(docOut.createTextNode("Null"));
    				root.appendChild(nullNode);
    			}
    
    			DOMSource source = new DOMSource(docOut);
    			StreamResult result = new StreamResult(out);      
    			transformer.transform(source, result);
    
    		} catch (FileNotFoundException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (ParserConfigurationException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (SAXException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (TransformerConfigurationException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (TransformerException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		finally {
    			if(in != null){
    				try {
    					in.close();
    				} catch (IOException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    			if(out != null){
    				try {
    					out.close();
    				} catch (IOException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    		}
    
    	
    

    This is the main method of a class that can be used in local machine.

    You have to modify it a bit to implement the interface "StreamTransformation" in order

    to use it as mapping program.

    The method does this:

    xml source :

    <?xml version="1.0" encoding="UTF-8"?>
    <HADR>
      <HADR_ST1>Null</HADR_ST1>
      <HADR_ST2>London st</HADR_ST2>
      <HADR_ST3>E1W 1S4</HADR_ST3>
      <HADR_TOWN>Null</HADR_TOWN>
      <HADR_CTY>London</HADR_CTY>
      <HADR_CTRY>Null</HADR_CTRY>
    </HADR>
    

    xml target:

    <?xml version="1.0" encoding="utf-8"?>
    <Addresses>
      <Address>London st</Address>
      <Address>E1W 1S4</Address>
      <Address>London</Address>
      <Address>Null</Address>
      <Address>Null</Address>
      <Address>Null</Address>
    </Addresses>
    

    If you have different node names, change the method of the class.

    Regards,

    Emiliano

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi,

      like I mentioned above use 7 input parameters:

      HADR_ST1 (input1)

      HADR_ST2 (input2)

      HADR_ST3 (input3)

      HADR_TOWN (input4)

      HADR_CTY (input5)

      HADR_CTRY (input6)

      AddressIndex (input7)

      Your function could look something like this:

      int indexNotNull = 0;

      if (!input1[0].equals(Null))

      indexNotNull++;

      if ((indexNotNull.toString()).equals(AddressIndex))

      return input1[0];

      if (!input2[0].equals(Null))

      indexNotNull++;

      if ((indexNotNull.toString()).equals(AddressIndex))

      return input2[0];

      ..

      Regards

      Patrick

  • avatar image
    Former Member
    Jun 11, 2008 at 10:04 AM

    Hi Sai,

    i think you can resolve your issue using normal message mapping.

    If is not a problem to have a target structure with the same name of every node, and the number of fields are always the same; you can simply define a target message type named Adressess with only one optional sub-element.

    In the message mapping you have to do a "duplicate subtree" in the sub-element n times, where n is the number of the target field x 2.

    In you exaple you have to do "duplicate subtree" to have 12 target field.

    Now, you can use the first six fileds for map the not-null source fields, and the last six fileds for map the null source fields.

    Using a simply "if-without-else", when a source field is null, only the second target filed will be created and viceversa.

    Hope you like this solution without writing a line of code.

    Add comment
    10|10000 characters needed characters exceeded