How to Find Repeated case based on certain condition

[P_Kale]

Hi,

I have a scenario where I want to tagged a policy with a remark "Case_Repeated" if same policy was logged after previous call resolved <= 56 days. e.g. is given below.

Policy_No	Call Logged date	Call Resolved date	Token_no	Repeted_Remark
123457	12-Mar-23	17-Mar-23	A_123
456789	17-Apr-23	20-Apr-23	B_124
123457	12-May-23	17-May-23	A_126	Case_Repeated
456789	19-Jun-23	12-Jul-23	B_123
123457	21-Jun-23	25-Jun-23	A_129	Case_Repeated
456789	26-Aug-23	31-Aug-23	B_127	Case_Repeated
123457	12-Dec-23	01-Feb-24	C_124
234567	09-Feb-24	15-Feb-24	d_123

Thanks in advance

@marcus_sommer 

@swuehl 

@Anil_Babu_Samineni 

[marcus_sommer · Accepted solution]

It could be done with interrecord-functions within a sorted resident load, like:

t: load *, ifNo = previous(No) and Date - previous(Date) <= 56, 'repeated', 'x') as Flag
resident X order by No, Date;

[Anil_Babu_Samineni]

@P_Kale Perhaps this?

If(Count(TOTAL <Policy_No> Policy_No_Seq)>1 and (Interval(Date(Date#([Call Resolved date] , 'DD-MMM-YY'))-Above(TOTAL Date(Date#([Call Resolved date] , 'DD-MMM-YY'))), 'dd'))<=56, 'Case_Repeated', Null()
)

Where in script, Good to create below field to identify the sequence number for Policy_No, 

AutoNumber(RecNo(), Policy_No) as Policy_No_Seq,

[P_Kale]

Hi @marcus_sommer 

Thanks for your support. This is working fine.

 But I am adding 1 more condition in it where only cases where Token No is generated those only required to be compared while flagging Repeat cases. I am attaching application in which I have just modified your given formula and it is working but only thing is where ever Token No is generated 1st time that cases also getting flagged as "Repeated". 

I have attached application as well as Input file and Out-put file in which I have marked cases in RED which should not be considered as Repeated as it is 1st time when Token No. is generated.

Thanks in advance.

[marcus_sommer]

You may just add this condition, too like:

ifNo = previous(No) and Date - previous(Date) <= 56 and len(Token), 'repeated', 'x') as Flag

[P_Kale]

Thanks @marcus_sommer 

I have tried by the suggestion you have given but there is no change in the out-put. The case which is highlighted in Red is not a repeat case as Token_no has generated 1st time only. Subsequent "Repeated' flagging is correct.

Can you pl check and suggest how to resolve this issue.

Policy_No	call_logged_date	call_Resolved_date	FLAG	Token_no
123457	12/03/2023	17/03/2023	-	-
123457	12/05/2023	17/05/2023	REPEATED	A_126
123457	21/06/2023	25/06/2023	-	-
123457	12/08/2023	31/08/2023	REPEATED	A_127
123457	15/09/2023	30/09/2023	REPEATED	A-_128
123457	12/12/2023	01/02/2024	-	-

Thanks in advance.

[marcus_sommer]

I'm not sure if this condition could be practically and sensible included within the above shown check else being flagged in a separate logic and finally evaluated afterwards, maybe like:

t: load
     *,
      if(IDCounter > 1 and Offset <= 56 and TokenCounter > 1 and TokenExists, 1, 0) as Flag;
   load
     *,
     ifNo = previous(No), Date - previous(Date)) as Offset,
     ifNo = previous(No), peek('IDCounter') + 1, 1) as IDCounter,
     sign(len(Token)) as TokenExists,
     ifNo = previous(No), peek('TokenCounter') + sign(len(Token))) as TokenCounter,
resident X order by No, Date;

Means mainly to simplify each single step by creating further useful information and using them within the final evaluation. It's not mandatory a detour because getting a ordered number of ID + Token and if Token exists and any Offset values and various similar information between them is usually very helpful for a lot of views.

[P_Kale]

Hi @marcus_sommer below given is the change done in the script. But flag value which I am getting is "0" only for all records.

TEST:

LOAD
Policy_No,
"Call Logged date" as call_logged_date,
"Call Resolved date"as call_Resolved_date,
Token_no,
Policy_No&'_'&"Call Logged date"&'_'&Token_no as LINK
FROM [lib://AttachedFiles/saple case_repeat.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

TEST_1:

Load *,

if(IDCounter > 1 and Offset <= 56 and TokenCounter > 1 and TokenExists, 1, 0) as Flag;

Load *,

if(Policy_No = Previous(Policy_No),call_logged_date - Previous(call_Resolved_date) ) as Offset,
if(Policy_No = Previous(Policy_No),peek('IDCounter') + 1, 1) as IDCounter,
sign(len(Token_no)) as TokenExists,
if(Policy_No = Previous(Policy_No),peek('TokenCounter') + sign(len(Token_no))) as TokenCounter

Resident TEST;

Drop Table TEST;

[marcus_sommer]

There is no order by statement included by the resident load of TEST. Beside this I suggest to include record-information in the load, like:

TEST_1: Load *, rowno() as RowNo,
if(IDCounter > 1 and Offset <= 56 and TokenCounter > 1 and TokenExists, 1, 0) as Flag;

Load *, recno() as RecNo;
....;

and to include them with the final-flag and all intermediate-flags within a table-box to see each single evaluation in the load-order and identifying which may not be working like expected and may need some logically and/or syntactically adjustment.

[P_Kale]

Thanks @marcus_sommer I have included the portion of the script which i have missed initially. Since I am using this functions 1st time finding little difficulty in using in script. Is i am missing something as I am getting 1 to 13 numbers under both the expressions.

TEST:

LOAD
Policy_No,
"Call Logged date" as call_logged_date,
"Call Resolved date"as call_Resolved_date,
Token_no,
Policy_No&'_'&"Call Logged date"&'_'&Token_no as LINK
FROM [lib://AttachedFiles/saple case_repeat.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

TEST_1:

Load *,rowno() as RowNo,

if(IDCounter > 1 and Offset <= 56 and TokenCounter > 1 and TokenExists, 1, 0) as Flag;

Load *,

if(Policy_No = Previous(Policy_No),call_logged_date - Previous(call_Resolved_date) ) as Offset,
if(Policy_No = Previous(Policy_No),peek('IDCounter') + 1, 1) as IDCounter,
sign(len(Token_no)) as TokenExists,
if(Policy_No = Previous(Policy_No),peek('TokenCounter') + sign(len(Token_no))) as TokenCounter,
recno() as RecNo
Resident TEST order by Policy_No,call_logged_date, ;

Drop Table TEST;