on 12-23-2010 1:49 PM
Dear Experts,
I'm facing the following problem :
I have a flat file with invoice data,
invoice number - line type - field name - field value
000101 - H - Invoice Date - 01.02.2010
000101 - H - Invoice TYpe - ZZZ
000101 - C - Customer Name - John Doe
000101 - C - Customer Address - XXX avenue
000101 - L1 - Line Item - 60003
000101 - L1 - Material - 98765
000101 - L1 - Net Value - 100,00
000101 - L2 - Line Item - 60004
000101 - L2 - Material - 98766
000101 - L2 - Net Value - 200,00
000102 - H - Invoice Date - 01.03.2010
000102 - H - Invoice TYpe - ZZZ
000102 - C - Customer Name - John Doe 2
000102 - C - Customer Address - yyy avenue
000102 - L1 - Line Item - 60001
000102 - L1 - Material - 98763
000102 - L1 - Net Value - 199,00
000102 - L2 - Line Item - 60002
000102 - L2 - Material - 98764
000102 - L2 - Net Value - 299,00
( H,C,and L* stands for Header,Customer,Line item )
and want somehow to convert it into XI to structured XML like this
output
-->Header ( 1:Unbound )
--> InvoiceDate
--> InvoiceType
-
> Customer ( 1:1)
-
> CustName
-
>CustAddr
-
> Item ( 1:unbound)
-
> ItemNo
-
>Matnr
-
>Value
Can somebody quide me through the mapping process ?
Tried with predefined functions ( splitpervalue,createif etc ) but with no luck.
Can somebody give me a hint !!
Edited by: Laurent Fournier on Dec 23, 2010 2:50 PM
Hi,
if u want the structure as
Header
Customer
LineItem
Header
Customer
LineItem
Header
Customer
LineItem
Please give the recordStructure as Header,,Customer,,LineItem,*
This will take care the whole structre.
Specify the keyfields as H,C and L instead of the invoice number
Babu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Laurent,
If I am not missing any understanding then it appears that you are not using content conversion for your sender file adapter. Your subjuect of the thread says that you are having a Flat file at the sender side. If this is the case then you can not map your source structure to target structure without content conversion or getting the flat file in a xml format. PI works only on xml so you need to go with content conversion and get a xml and then only you can do any transformations inside the PI.
Regards,
Rahul
Hi,
Sorry i think i misunderstood the question!!!
Let us first understand exact requirement in the mapping, then some1 here can give u the solution..
For the i/p u have given in the post...
Please check is it the output expected......
Invoice Date - 01.02.2010
Invoice TYpe - ZZZ
Invoice Date - 01.03.2010
Invoice TYpe - ZZZ
Customer Name - John Doe
Customer Address - XXX avenue
Line Item - 60003
Material - 98765
Net Value - 100,00
Line Item - 60004
Material - 98766
Net Value - 200,00
Line Item - 60001
Material - 98763
Net Value - 199,00
Line Item - 60002
Material - 98764
Net Value - 299,00
This may be wrong because in above o/p the only 1 customer details given with all line items.
I mapped so because the occurence of customer is 1:1.
or is this o/p is correct?
Invoice Date - 01.02.2010
Invoice TYpe - ZZZ
Customer Name - John Doe
Customer Address - XXX avenue
Line Item - 60003
Material - 98765
Net Value - 100,00
Line Item - 60004
Material - 98766
Net Value - 200,00
Invoice Date - 01.03.2010
Invoice TYpe - ZZZ
Customer Name - John Doe 2
Customer Address - yyy avenue
Line Item - 60001
Material - 98763
Net Value - 199,00
Line Item - 60002
Material - 98764
Net Value - 299,00
Babu
Hi Rahul,
Flat file is being converted to simple xml structure in XI using content conversion.
So example file (txt)
000101 - H - Invoice Date - 01.02.2010
000101 - H - Invoice TYpe - ZZZ
000101 - C - Customer Name - John Doe
000101 - C - Customer Address - XXX avenue
will be converted to xml ( inside XI )
<LINE>
<Field1>000101 </Field1>
<Field3>H</Field3>
<Field4>InvoiceDate</Field4>
<Field5>01.02.2010 </Field5>
</LINE>
<LINE>
<Field1>000101 </Field1>
<Field3>H</Field3>
<Field4>InvoiceType</Field4>
<Field5>ZZZ </Field5>
</LINE>
<LINE>
<Field1>000101 </Field1>
<Field3>C</Field3>
<Field4>CustomerName</Field4>
<Field5>John Doe</Field5>
</LINE>
<LINE>
<Field1>000101 </Field1>
<Field3>C</Field3>
<Field4>CustomerAddress</Field4>
<Field5>XXX Avenue</Field5>
</LINE>
The target xml structure is ( inside XI )
<HEADER>
<InvoiceNo>000101</InvoiceNo>
<InvoiceDT>01.02.2010</InvoiceDT>
<InvoiceTY>ZZZ</InvoiceTY>
<CUSTOMER>
<CustomerName>John Doe</CustomerName>
<CustomerAddress>xxx Avenue</CustomerAddress>
</CUSTOMER>
<ITEM></ITEM>
<ITEM></ITEM>
<ITEM></ITEM>
</HEADER>
Target File ( txt )
invoice,invoice type,invoice date,customer name,customer address,line item 1, material number,value
invoice,invoice type,invoice date,customer name,customer address,line item 2, material number,value
invoice,invoice type,invoice date,customer name,customer address,line item 3, material number,value
Problem : How to do message mapping in order to go from source structure to target structure ???
Is it possible ? As i understand we need to implement the opposite of "one node to many".
Hope now is clear to understand the exact requirement.
Hi,
Before some1 here provide u how to map for this case....
How are u accessing the source file FCC?
Does the contents are coming like ,
000101 - H - Invoice Date - 01.02.2010
000101 - H - Invoice TYpe - ZZZ
000101 - C - Customer Name - John Doe
000101 - C - Customer Address - XXX avenue
000101 - L1 - Line Item - 60003
000101 - L1 - Material - 98765
000101 - L1 - Net Value - 100,00
000101 - L2 - Line Item - 60004
000101 - L2 - Material - 98766
000101 - L2 - Net Value - 200,00
000102 - H - Invoice Date - 01.03.2010
000102 - H - Invoice TYpe - ZZZ
000102 - C - Customer Name - John Doe 2
000102 - C - Customer Address - yyy avenue
000102 - L1 - Line Item - 60001
000102 - L1 - Material - 98763
000102 - L1 - Net Value - 199,00
000102 - L2 - Line Item - 60002
000102 - L2 - Material - 98764
000102 - L2 - Net Value - 299,00
The whole record as a single element or are u doing any other FCC?
Let us know the structure of source so that some1 can provide u the Mapping..
Babu
Two or more deep xml structure conversion is not possible in file content conversion . If you want to convert into the below structure i.e at the same level , then it is possible.
Header(1 to 1)
-->Invoice Date
-->Invoice Type
Customer (1 to 1)
--->customer name
--->customer address
LineItem (1 to unbounded)
--->itemno
--->Matnr
---> Value
Please try this link. This might be helpful to you.
/people/shabarish.vijayakumar/blog/2006/02/27/content-conversion-the-key-field-problem
Note: I heard from Shabrish weblog that Conversion Agent tool will be helpful to do deep nested xml structure.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I 'm not trying to use file content conversion.
I'm trying to convert the data using message mapping.
Source Message :
Line ( 1:unbounded)
--> Field1
--> Field2
--> Field3
--> Field4
Target Message
-->Header ( 1:Unbound )
--> InvoiceDate
--> InvoiceType
-
> Customer ( 1:1)
-
> CustName
-
>CustAddr
-
> Item ( 1:unbound)
-
> ItemNo
-
>Matnr
-
>Value
Field3 in source message describes target field in target message.
Just For Clarification...The example bellow is for the header data only. The Same applies on customer and items data.
Inbound File :
000101 - H - Invoice Date - 01.02.2010
000101 - H - Invoice TYpe - ZZZ
Target Strcucture :
Header (1:unbounded)
--> InvoiceNo - 000101
--> InvoiceDT - 01.02.2010
--> InvoiceTP - ZZZ
-
> Customer (1:1)
-
> Items (1:Unbounded)
So to be clear.. The first line of the inbound file should produce a line in the header and fill the header field Invoice No and invoice date.The Second line of the inbound file must NOT produce a new line in header but Fill in the InvoiceTy field with the corresponding value.
Any Hints to achive this with Message Mapping ?
User | Count |
---|---|
80 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.