Creating hierarchy in data column

[ahammadshafi]

Dear Experts:

My client wants to view a table with a hierarchy (Region-> Sub-Region -> Country).

But they all come in a single column, "Country".

Please find attached sample data.

My question would be, what is the best approach to create the hierarchy while/after loading the data? And how should I create the table in desired format (with proper spacing, parent->child like Nordics-> Norway)?

Ahammad Shafi

[t_chetirbok · Accepted solution]

Hi!

Find attachment! !

It's hierarchy and pivot table, but I'm not sure that it is possible to do vertical pivot

[agustinbobba]

Hi Ahammad,

First, you need to add the ID for the Node and the Parent. I attach the Excel file with the example.

Then you can use the Hierarchy Logic.

Aux:
LOAD Geo_Level,
     Country, 
     ParentID, 
     CountryID,
     Product, 
     Type, 
     Period, 
     Value
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, table is Data);




NoConcatenate
Hierarchy(CountryID, ParentID, Country) 
LOAD Distinct
  CountryID,
  ParentID,
  Country
Resident Aux;


Table:
LOAD Geo_Level,
     CountryID,
     Product, 
     Type, 
     Period, 
     Value
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, table is Data);




drop Table Aux;


EXIT Script;

The result is something like that:

Regards,

Agustin