parsing hierarchical data

[dvkumaraws2019]

Hi,
I am having some challenges parsing hierarchical data into a tabular format. The raw data are stored as a “grouped” format as below.

This grouped hierarchical data needs to be transformed as below -

I tried to use a row filter, but only I could get particular rows.

The excel is attached to this forum for reference.
to_share.xlsx (10.2 KB)

Since the data are in columns and its is stored in a series, I am having issues extracting them as “sets” of data and changing to tabular data form.

[mlauber71]

@dvkumaraws2019 my impression is that the example is not complete and/or not consistent. Can you check again and maybe further explain the ‚rules‘?

What would constitute a ‚Level 2‘ and what happened to the „EG_ATTR01“ of V01 block?

[dvkumaraws2019]

The raw data is basically an extract of a data dictionary.

The id column consists of both entity and attribute names.

If a row contains type as “G” and level1 as “G”, then it’s an Entity. and all the rows after that with type A and level2 with “M” meaning Mandatory are the attributes of that entity.

There may be several versions of the entity, so I would like to have only the version with the highest value. So for example - V03 is the valid entity.

So the output should be

Special Cases
There is some case, where an entity may be referred to another entity. This is referenced by level2 with the status as “G”. For example in CAR entity, EG entity is refered.

so the output should like -

[mlauber71]

@dvkumaraws2019 I gave it a try and hope I have covered all cases you mentioned.

extract hierachical data in special format – mlauber71

[dvkumaraws2019]

Thanks @mlauber71 , it worked like a charm