topic Re: Pick characterse before “-” and then compare in QlikView

Pick characterse before “-” and then compare

capriconuser — Wed, 24 Jul 2019 09:38:54 GMT

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

Re: Pick characterse before “-” and then compare

Zhandos_Shotan — Wed, 24 Jul 2019 10:16:27 GMT

Hi!

Use Subfield function:

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

also, check attachment

Kind regards

Re: Pick characterse before “-” and then compare

capriconuser — Wed, 24 Jul 2019 11:09:48 GMT

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

Re: Pick characterse before “-” and then compare

sunny_talwar — Wed, 24 Jul 2019 11:25:33 GMT

Have you tried just this

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

Re: Pick characterse before “-” and then compare

Zhandos_Shotan — Wed, 24 Jul 2019 11:30:02 GMT

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

Re: Pick characterse before “-” and then compare

capriconuser — Wed, 24 Jul 2019 12:37:48 GMT

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"

Re: Pick characterse before “-” and then compare

Zhandos_Shotan — Fri, 26 Jul 2019 10:54:26 GMT

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