FlatToHierarchyLoop Example

Highlighted
Five Stars

FlatToHierarchyLoop Example

Hi:

I am trying to use the FlatToHierarchyLoop, but I can't solve my problem. I have the following information:

 

ID|COUNTRY|STATE_ID|STATE

1|USA|1|TN

1|USA|2|NY

1|USA|3|KY

2|PERU|4|LIM

2|PERU|5|CUZ

 

I expect the following result:

<root>
        <ID>USA</ID>
       <states>
             <state>

                    <state_id>1</state_id>

                    <state>TN</state>

             </state>

             <state>

                    <state_id>2</state_id>

                    <state>NY</state>

             </state>

       </states>

</root>

 

Please your help to give me the solution with FlatToHierarchyLoop function in Data Mapper.



 

 

 


Accepted Solutions
Highlighted
Employee

Re: FlatToHierarchyLoop Example

You probably need 2 loops on the output side, please see the attachment for details.

 

The first one on the country with a distinct option of ID os you can get 1 "USA" and 1 "PERU"

m1.png

The second on the state(s) with an equal-filter to group states of each country

m2.png

View solution in original post


All Replies
Highlighted
Moderator

Re: FlatToHierarchyLoop Example

Hello,

Have you already checked online user guide about:https://help.talend.com/reader/0G_VvrRkdPvPXhSX86ghZQ/92Pfh3XduPQRGJHLwYqNdg?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Highlighted
Employee

Re: FlatToHierarchyLoop Example

@xdshi

 

      I tried to replicate it quickly but I am also getting Data Mapper Problem without any error details. I have tried in DataFabric 7.0 and I have raised JIRA ticket for further reference. Could you please have a review?

 

https://jira.talendforge.org/browse/TDM-6568

 

Warm Regards,

 

Nikhil Thampi 

 

 

Highlighted
Employee

Re: FlatToHierarchyLoop Example

Hi,
The input contains 2 distinct countries "USA" and "PERU" but your expected output has only 1 non-repetitive ID "USA" under the root.

This can't work.

Maybe you need to add an element "country" to wrap "ID" and "states"

<root>
  <Country>
    <ID>USA</ID>
    <states>
      <state>
        <state_id>1</state_id>
        <state>TN</state>
      </state>
      <state>
        <state_id>2</state_id>
        <state>NY</state>
      </state>
      <state>
        <state_id>3</state_id>
        <state>KY</state>
      </state>
    </states>
  </Country>
  <Country>
    <ID>PERU</ID>
    <states>
      <state>
        <state_id>4</state_id>
        <state>LIM</state>
      </state>
      <state>
        <state_id>5</state_id>
        <state>CUZ</state>
      </state>
    </states>
  </Country>
</root>
Highlighted
Employee

Re: FlatToHierarchyLoop Example

You probably need 2 loops on the output side, please see the attachment for details.

 

The first one on the country with a distinct option of ID os you can get 1 "USA" and 1 "PERU"

m1.png

The second on the state(s) with an equal-filter to group states of each country

m2.png

View solution in original post

Highlighted
Employee

Re: FlatToHierarchyLoop Example

Thanks @hnie for the explanation with right screen shots :-)

 

I learnt something new today! 

 

Warm Regards,

 

Nikhil Thampi

 

 

Highlighted
Employee

Re: FlatToHierarchyLoop Example

Hi,

 

probably what you want to do is grouping the states by country id (see below).

<root>
  <states>
    <ID>USA</ID>
    <state>
      <state_id>1</state_id>
      <state_name>TN</state_name>
    </state>
    <state>
      <state_id>2</state_id>
      <state_name>NY</state_name>
    </state>
    <state>
      <state_id>3</state_id>
      <state_name>KY</state_name>
    </state>
  </states>
  <states>
    <ID>PERU</ID>
    <state>
      <state_id>4</state_id>
      <state_name>LIM</state_name>
    </state>
    <state>
      <state_id>5</state_id>
      <state_name>CUZ</state_name>
    </state>
  </states>
</root>

In order to do that, you have to define the output structure like that:

struct_out.png

Then you assign the COUNTRY input element to the ID output element, and you set the "distinct child element" parameter of the states loop:

distinct_child_element.png

 

Finally you assign STATE_ID and STATE input elements to state_id and state_name output elements, and you specify a loop expression on the state output element with a filter:

inner_loop_with_filter.png

 

Regards,

 

Tony

Highlighted
Five Stars

Re: FlatToHierarchyLoop Example

Hi @hnie:

 

Thanks for your help. IS appreciated a lot your time. I tried to use that you mentioned but I have a problem when I implement this strategy in this scenario.

 

I need to do many loops in the output structure from a flat structure. Do have any idea how do I solve that? 

 

OUTPUT

<root>
<Capital>1</Capital>
<Code>AFG</Code>
<Code2>AF</Code2>
<Continent>Asia</Continent>
<GNP>5976</GNP>
<GNPOld null="true" />
<GovernmentForm>Islamic Emirate</GovernmentForm>
<HeadOfState>Mohammad Omar</HeadOfState>
<IndepYear>1919</IndepYear>
<LifeExpectancy>45.9</LifeExpectancy>
<LocalName>Afganistan/Afqanestan</LocalName>
<Name>Afghanistan</Name>
<Population>22720000</Population>
<Region>Southern and Central Asia</Region>
<SurfaceArea>652090</SurfaceArea>
<cities>
<element>
<CountryCode>AFG</CountryCode>
<District>Kabol</District>
<ID>1</ID>
<Name>Kabul</Name>
<Population>1780000</Population>
</element>
<element>
<CountryCode>AFG</CountryCode>
<District>Qandahar</District>
<ID>2</ID>
<Name>Qandahar</Name>
<Population>237500</Population>
</element>
<element>
<CountryCode>AFG</CountryCode>
<District>Herat</District>
<ID>3</ID>
<Name>Herat</Name>
<Population>186800</Population>
</element>
<element>
<CountryCode>AFG</CountryCode>
<District>Balkh</District>
<ID>4</ID>
<Name>Mazar-e-Sharif</Name>
<Population>127800</Population>
</element>
</cities>
<languages>
<element>
<CountryCode>AFG</CountryCode>
<IsOfficial>F</IsOfficial>
<Language>Balochi</Language>
<Percentage>0.9</Percentage>
</element>
<element>
<CountryCode>AFG</CountryCode>
<IsOfficial>T</IsOfficial>
<Language>Dari</Language>
<Percentage>32.1</Percentage>
</element>
<element>
<CountryCode>AFG</CountryCode>
<IsOfficial>T</IsOfficial>
<Language>Pashto</Language>
<Percentage>52.4</Percentage>
</element>
<element>
<CountryCode>AFG</CountryCode>
<IsOfficial>F</IsOfficial>
<Language>Turkmenian</Language>
<Percentage>1.9</Percentage>
</element>
<element>
<CountryCode>AFG</CountryCode>
<IsOfficial>F</IsOfficial>
<Language>Uzbek</Language>
<Percentage>8.8</Percentage>
</element>
</languages>
</root>

 

Highlighted
Employee

Re: FlatToHierarchyLoop Example

Hi,
Could you share your project to get the input?
Highlighted
Five Stars

Re: FlatToHierarchyLoop Example

@hnie

 

I attached the mapper example

Highlighted
Employee

Re: FlatToHierarchyLoop Example

@CJIGA
It seems to be a different issue, could you create a new topic with Label "Talend Data Mapper" for it, please?

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Creating a Stewardship Campaign and Data Model

In this video, you will see how to create a new campaign and enable team members to address curation tasks assigned to them within an approval workflow process

Watch Now

Managing a Stewardship Campaign

In this video, you will see how to assign your best team members specific tasks to reconcile, correct, merge, arbitrate or group pre-determined data and achieve quality, clean data in a limited time

Watch Now

Talend Data Stewardship – What does it have to offer?

In this short series, you will see how Talend Data Stewardship transforms your employees into data citizens and enables them with self-service capabilities to control their quality data

Watch Now