How to replace value of champ

[Fred12]

Hello, I got a table like it :

link                                      Value

AA_2021.xlsm                 P0

AA_2021.xlsm                 P1

AA_2021.xlsm                P2

AA_2022.xlsm               P0

AA_2022.xlsm              P1

AA_2022.xlsm              P2

And I would like replace all P0,P1,P2 by the value in link but +1 or +2 if it is P1 or P2.  

I explain myself :

link                                      New_Value

AA_2021.xlsm                 2021

AA_2021.xlsm                 2021+1

AA_2021.xlsm                2021+2

AA_2022.xlsm               2022

AA_2022.xlsm              2022+1

AA_2022.xlsm              2022+2

I tried to use replace, extract .. But it give me always error ,  thanks for reading me

[SunilChauhan]

Test:

Load link,value from path;

New_Test:

Load  *,

tempnewvalue+ num(right(value,1)) as  New_Value;

Load link, value,

Num(Subfield(Subfield(link,'.',1),'_',2)) as tempnewvalue

Resident Test;

Drop table test;

Now load and take columns link and New_Value in table and see the result.

Hope this helps

[Saravanan_Desingh · Accepted solution]

Try this,

tab1:
LOAD *, Mid(link,4,4)&If(Right(Value,1)>0,'+'&Right(Value,1)) As New_Value;
LOAD * INLINE [
link, Value
AA_2021.xlsm, P0
AA_2021.xlsm, P1
AA_2021.xlsm, P2
AA_2022.xlsm, P0
AA_2022.xlsm, P1
AA_2022.xlsm, P2
];

[Fred12]

Thanks a lot, it works perfectly !

[SunilChauhan]

Mark as correct to close this post.