Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
capriconuser
Creator
Creator

Pick characterse before “-” and then compare

Hi i have a data like this image

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

2 Solutions

Accepted Solutions
Zhandos_Shotan
Partner - Creator II
Partner - Creator II

Hi!

Use Subfield function:

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

 

also, check attachment

 

Kind regards

 

View solution in original post

Zhandos_Shotan
Partner - Creator II
Partner - Creator II

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

 

View solution in original post

6 Replies
Zhandos_Shotan
Partner - Creator II
Partner - Creator II

Hi!

Use Subfield function:

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

 

also, check attachment

 

Kind regards

 

capriconuser
Creator
Creator
Author

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
Partner - Creator II
Partner - Creator II

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
Creator
Creator
Author

ok one more thing i want to ask 

in this picture

1.PNG

 

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"