Analytic Structure of account

Question

hello all,

for the attached data "IFC_mapping" I m trying to develop a flow to aggregate the data in the following format :

PRC 1

PRC 2

...

PRC n

Agregate LV1

PRC 1

PRC 2

...

PRC n

Aggregate LV2

PRC 1

PRC 2

...

PRC n

Aggregate LV3

PRC 1

PRC 2

...

PRC n

Aggregate LV4

PRCs mostly gather in Aggregate LVL1, Aggregate LVL1 gather in Aggregate LVL2 and so on, but there are cases when the PRC is not allocated to Agergate LVL 1 and goes directly in Aggregate LVL2 , 3 or even 4.

I ve tried in several ways but didnt  managed to aggregate all PRC s and create the pyramide. Could you pls help on this if you worked already on a structure like this.

thanks in advance !

roxana

IFC_mapping.xlsx

apathetichell · Answer

Hi @RoxanaOlteanu I don't really know but this might be close to what you want? I think you are transposing columns and getting a list of which codes appear in which columns. A few notes - you have LV in all of your output but your column headers in much of of your input are LVL I adjusted this to check  - I'm not sure which is correct...

mapping.yxmd

KarolinaRoza · Answer

Hi @RoxanaOlteanu

Can you explain why you are aggregating PRC sometimes by one LVL and sometimes by few LVLs.

I mean in the output  there are rows like:

IA12100PRCIAT011200LV2IAT011000LV3IAT010000LV4IA36218PRC IA36220PRC IA36222PRC IA36227PRC IAT021110LV1IA36210PRCIA36212PRCIA36217PRCIA36226PRCIAT021120LV1IAT021100LV2

There are only one row with LV (like LV1 - 9 row) and then there are several rows like (LV1, LV2 - rows 14-15 ). Why?

It is still not clear for me..

Karolina

RoxanaOlteanu · Answer

hello

I ve added in the attchement a sample of the output .

thanks!

roxana

IFC_mapping.xlsx