FlatToHierarchyLoop Example

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
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


All Replies
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.
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 

 

 

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>
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

Employee

Re: FlatToHierarchyLoop Example

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

 

I learnt something new today! 

 

Warm Regards,

 

Nikhil Thampi

 

 

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

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?
Five Stars

Re: FlatToHierarchyLoop Example

@hnie

 

I attached the mapper example

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 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch