topic Re: FlatToHierarchyLoop Example in Talend Data Catalog

FlatToHierarchyLoop Example

CJIGA — Sat, 16 Nov 2024 07:29:25 GMT

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:

<state_id>1</state_id>

</state>

<state>

<state_id>2</state_id>

</state>

</states>

</root>

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

Re: FlatToHierarchyLoop Example

Anonymous — Tue, 16 Oct 2018 06:31:45 GMT

Hello,

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

Best regards

Sabrina

Re: FlatToHierarchyLoop Example

Anonymous — Thu, 18 Oct 2018 09:49:15 GMT

@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

Re: FlatToHierarchyLoop Example

Haitao — Thu, 18 Oct 2018 10:46:44 GMT

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>

Re: FlatToHierarchyLoop Example

Haitao — Thu, 18 Oct 2018 11:11:36 GMT

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"

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

tdm-demo.zip

Re: FlatToHierarchyLoop Example

Anonymous — Thu, 18 Oct 2018 11:30:23 GMT

Thanks @hnie for the explanation with right screen shots 🙂

I learnt something new today!

Warm Regards,

Nikhil Thampi

Re: FlatToHierarchyLoop Example

timbault — Thu, 18 Oct 2018 14:29:22 GMT

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:

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

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:

Regards,

Tony

Re: FlatToHierarchyLoop Example

CJIGA — Thu, 18 Oct 2018 22:31:45 GMT

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>

TalendMapper.PNG

Re: FlatToHierarchyLoop Example

Haitao — Fri, 19 Oct 2018 09:28:22 GMT

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

Re: FlatToHierarchyLoop Example

CJIGA — Fri, 19 Oct 2018 18:24:30 GMT

@hnie

I attached the mapper example

MapperExample.zip

Re: FlatToHierarchyLoop Example

Haitao — Tue, 23 Oct 2018 11:00:52 GMT

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