if - chart function

[MJlassi]

Hello,

Can someone help with the below?

I have the following data:

the desired result is as follows:

I would like to develop an expression that serves to reach the column 'EXP'

Thank you in advance for your help.

[QFabian · Accepted solution]

Hi @MJlassi ,  please check this formula, i just created a straight table with the two dimnsions, and one expression :

if(rowno()=1 and left(Test, 2) = 'OK', 'THIS',
if( left(Test, 2) = 'OK' and Code = above(Code)and above(column(1)) <> 'THIS' and above(above(column(1))) <> 'THIS',
'THIS', 'NOT THIS'))

[Saravanan_Desingh · Accepted solution]

Try this in Script,

tab1:
LOAD * INLINE [
    Code, Test
    A, OKK
    A, KO
    B, OKKK
    B, KO
    B, KO
    C, OK
    C, OKK
    C, OKKK
    C, KO
];

Left Join(tab1)
LOAD Code, Concat(DISTINCT '@'&Test&'@') As C1
Resident tab1
Group By Code;

Left Join(tab1)
LOAD Code, Test, If(Test='OK' Or (Test Like '*OK?*' And Index(C1,'@OK@')=0),'THIS','NOT THIS') As EXP
Resident tab1
;

Drop Field C1;

[Saravanan_Desingh]

Output: