Split flat file into two level hierarchy based on field field value

[former_member214137]

Hi,

I have a source file where a row contains both header and line item data, therefore there may be multiple rows with the same header data information.I want to be able to produce a two level hierarchy target with the source data being split based on a change of value for one of the header fields, then I will end up with a single header with multiple line items for every header value change.

I have been able to split in about every combination possible except the one I need and want.

Source

<?xml version="1.0" encoding="UTF-8"?>

<ns0:test_hierarchy_mt xmlns:ns0="urn:ZTEST_JULIAN3">

   <input>

      <h1>1</h1>

      <h2>2</h2>

      <l1>a</l1>

      <l2>b</l2>

   </input>

   <input>

      <h1>1</h1>

      <h2>2</h2>

      <l1>c</l1>

      <l2>d</l2>

   </input>

   <input>

      <h1>3</h1>

      <h2>4</h2>

      <l1>e</l1>

      <l2>f</l2>

   </input>

</ns0:test_hierarchy_mt>

Resulting target wanted

<?xml version="1.0" encoding="UTF-8"?>

<ns0:ouput_mt xmlns:ns0="urn:ZTEST_JULIAN3">

   <header h1="1" h2="2">

      <lines>

         <l1>a</l1>

         <l2>b</l2>

      </lines>

      <lines>

         <l1>e</l1>

         <l2>d</l2>

      </lines>

   </header>

   <header h1="3" h2="4">

      <lines>

         <l1>e</l1>

         <l2>f</l2>

      </lines>

   </header>

</ns0:ouput_mt>

</ns0:test_hierarchy_mt>

thanks in advance

Julian

[former_member184681 · Accepted solution]

Hi,

Use the following mappings (assuming that the input is sorted by h1 and h2):

h1 (context: test_hierarchy_mt) -> splitByValue(Value Change) -> collapseContexts -> header

h1 -> h1, h2 -> h2

h1 (context: test_hierarchy_mt) -> splitByValue(Value Change) -> lines

l1 -> l1, l2 -> l2

Also, you might want to have a look at my blog below for a review of the queues & contexts concept of graphical mapping:

Regards,

Greg

[former_member214137 · Accepted solution]

Hi Greg,

Thanks for the quick reply. Still not working for me though, am I missing something? I'm 95% sure I had tried this.

Here is my mapping:

/ns0:ouput_mt=/ns0:test_hierarchy_mt
/ns0:ouput_mt/header=collapseContexts(SplitByValue(/ns0:test_hierarchy_mt/input/h1, type=2))
/ns0:ouput_mt/header/@h1=/ns0:test_hierarchy_mt/input/h1
/ns0:ouput_mt/header/@h2=/ns0:test_hierarchy_mt/input/h2
/ns0:ouput_mt/header/lines=SplitByValue(/ns0:test_hierarchy_mt/input/h1, type=2)
/ns0:ouput_mt/header/lines/l1=/ns0:test_hierarchy_mt/input/l1
/ns0:ouput_mt/header/lines/l2=/ns0:test_hierarchy_mt/input/l2

and my result set:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:ouput_mt xmlns:ns0="urn:ZTEST_JULIAN3">

   <header h1="1" h2="2">

      <lines>

         <l1>a</l1>

         <l2>b</l2>

      </lines>

      <lines>

         <l1>c</l1>

         <l2>d</l2>

      </lines>

   </header>

</ns0:ouput_mt>

Also thanks for the link to your blog.

many thanks Julian

[former_member184681 · Accepted solution]

Your results seem almost correct, don't they? Just two things to verify:

1. Make sure the header tag in output_mt has occurrence 1..unbounded or 0..unbounded. Otherwise you won't be able to create multiple header tags in the output.

2. If #1 above is correct, please share your queue contents for the header tag for your example message.

I know these are basic things, but in developing complex scenarios, it is quite easy to miss such details.

Regards,

Greg

[former_member214137 · Accepted solution]

Hi Greg,

Magic!! It was just the occurrence that was the issue.

Again thank you for your help.

regards

Julian