Take the value of column that satify condition and apply it for other columns

[Anonymous]

Hello friends,

I have a challenged question.

I have this data: 

FirstLevelActivitycode	SecondLevelActivitycode	ThirdLevelActivitycode	Label	ParentCode
A	B		Naxos	A|B
A	C		Mikonos	A|C
A	C	F	Athene	A|C | F
A	D		Piros	A|D
A			Salamina	A|
A	C	G	Athos	A|C | G
A	E		Lefkada	A|E

I already use the function Path and Pathitem to get the firstlevelactivitycode and second ...

My goal is to have this data: 

FirstLevelActivitycode	SecondLevelActivitycode	ThirdLevelActivitycode	Label	ParentCode	FirstLevelLabel	SecondLevelLabel	ThirdLevelLabel
A	B		Naxos	A|B	Salamina	Salamina | Naxos
A	C		Mikonos	A|C	Salamina	Salamina | Mikonos
A	C	F	Athene	A|C | F	Salamina	Salamina | Mikonos	Salamina | Mikonos | Athene
A	D		Piros	A|D	Salamina	Salamina | Piros
A			Salamina	A	Salamina
A	C	G	Athos	A|C | G	Salamina	Salamina | Mikonos	Salamina | Mikonos | Athos
A	E		Lefkada	A|E	Salamina	Salamina | Lefkada

The idea now is to say that for example if secondlevel & third level, memorize this label for this [A]  FirstLevelActivitycode, and put the label in all the rows of FirstLevelLabel. 

I think of something like that put does not work correctly:

FirstLevelLabel = IF(ISBLANK([SecondLevel]) || ISBLANK([ThirdLevel]) || keco_ref_activity_fields[FirstLevelActivtycode] = "O_738"  ;RELATED(keco_ref_activity_fields[Label]);BLANK())

The same logic will be for SecondLevelLabel with this idea: 

SecondLevelLabel = CONCATENATE(IF(ISBLANK(keco_ref_activity_fields[SecondLevel]);Blank();   keco_ref_activity_fields[FirstLevelLabel] & " | ")  ;   IF(AND(ISBLANK([FirstLevelLabel]);ISBLANK([ThirdLevel]));keco_ref_activity_fields[Label];BLANK())) 

Note that is one example, the idea is to generalize the solution so if we have FirstLevelActivitycode = A1 for example and other value, it make the same process.

Can anyone bring idea and help on this.

thank you.

Best regards,

Salem

[Anonymous]

Hi @Anonymous 

I have understood your requirement, but I have a query to confirm with you regarding your data, regarding the FirstLevelActivitycode column "A1" and the "O_738" mentioned in your formula, What is the form of their ParentCode? Is it O_738|xxx, or O?

Best Regards,
Yulia Xu

[Anonymous]

Hello, 

any solution for that.

thank you 🙂

Salem

[Anonymous]

Hi,

Thank you for your response.

The firstlevelactivtycode is one level: A or B ... 

I have the parentcode instead that contains the different level with |. 

But my main issue is to divide the label, the  levelactivitycode is already divided.

Best regards,

Salem