Analytic Structure of account

[RoxanaOlteanu]

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 

[KarolinaRoza]

hi @RoxanaOlteanu 

Could you include also example of desired output? 

I do not fully understand what you want to achieve,

Regards,

Karolina

[RoxanaOlteanu]

hello 

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

thanks!

roxana 

[KarolinaRoza]

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:

IA12100	PRC
IAT011200	LV2
IAT011000	LV3
IAT010000	LV4
IA36218	PRC
IA36220	PRC
IA36222	PRC
IA36227	PRC
IAT021110	LV1
IA36210	PRC
IA36212	PRC
IA36217	PRC
IA36226	PRC
IAT021120	LV1
IAT021100	LV2

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

[apathetichell]

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