Pick characterse before “-” and then compare

[capriconuser]

Hi i have a data like this

in this image i am trying to do that there is 122-1 in P NO column and in P_No_1 there is 122 so i want to compare that if in P NO column any value come before " - " must match to P_NO_1 if value is matched then this should be renew else lost ..

this is file

https://www.dropbox.com/sh/ytwjoflozm47dee/AACVuAJ-KI_DDz672i86kMtVa?dl=0

[Zhandos_Shotan · Accepted solution]

Hi!

Use Subfield function:

=if(P_No_1=SubField([P No.],'-',1), 'Renew','Lost')

also, check attachment

Kind regards

[capriconuser]

one thing i want to ask here.. 

after i tried i get solution what i want but there is another problem that 

here i apply condition on P No that whatever the value before " -1 " it should minus "-1" and paste value in P_No_1

if(Right([P No.],2)='-1',SubField( [P No.],'-',1)) as P_No_1

but here is incremental load thing apply on P NO that when polices increases with same P NO then increment apply   in data e.g. "P/03/Me/2018/00003-1" when another polices then this will be P/03/Me/2018/00003-2 when 3rd time assigen then  this will be P/03/Me/2018/00003-3

so here i want whatever the number after dash " - " this would be minus and before " - " sign whole value will paste in P_No_1 column 

how i do that.. 

updated file attached

[sunny_talwar]

Have you tried just this

SubField([P No.], '-', 1) as P_No_1

[Zhandos_Shotan · Accepted solution]

Not sure if i understand what you need, but try this:

if(

Right([P No.],2)='-'&Text(SubField( [P No.],'-',2)),

SubField( [P No.],'-',1)) as P_No_1

[capriconuser]

ok one more thing i want to ask 

in this picture

in these records some P NO year are 2019 so i want what ever polices are in 2019 and old policy no is not exist then this should be consider as new .. not lost.. how i do this 

and policies who is not found in previous year ie. 00013 policy is not found in previous year then this should be as "LOST"

[Zhandos_Shotan]

https://community.qlik.com/t5/New-to-QlikView/conditions-according-to-the-year/m-p/1606456#M378496