topic Re: How to Find Repeated case based on certain condition in App Development

How to Find Repeated case based on certain condition

P_Kale — Mon, 18 Mar 2024 03:33:25 GMT

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

Re: How to Find Repeated case based on certain condition

marcus_sommer — Mon, 18 Mar 2024 05:56:57 GMT

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;

Re: How to Find Repeated case based on certain condition

Anil_Babu_Samineni — Mon, 18 Mar 2024 05:59:25 GMT

@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,

Re: How to Find Repeated case based on certain condition

P_Kale — Mon, 18 Mar 2024 11:19:28 GMT

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.

Re: How to Find Repeated case based on certain condition

marcus_sommer — Mon, 18 Mar 2024 11:54:40 GMT

You may just add this condition, too like:

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

Re: How to Find Repeated case based on certain condition

P_Kale — Mon, 18 Mar 2024 12:13:22 GMT

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.

Re: How to Find Repeated case based on certain condition

marcus_sommer — Mon, 18 Mar 2024 12:44:12 GMT

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.

Re: How to Find Repeated case based on certain condition

P_Kale — Mon, 18 Mar 2024 13:21:12 GMT

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;

Re: How to Find Repeated case based on certain condition

marcus_sommer — Mon, 18 Mar 2024 13:43:49 GMT

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.

Re: How to Find Repeated case based on certain condition

P_Kale — Mon, 18 Mar 2024 14:54:29 GMT

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:

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;

Re: How to Find Repeated case based on certain condition

marcus_sommer — Mon, 18 Mar 2024 15:24:48 GMT

Results from 1 - 13 shouldn't be. But I just notice that it seems that you want not compare the date from the current record against the previous record else difference between logged-date and resolved date. In this case the Offset-calculation should be look like:

call_logged_date - call_Resolved_date as Offset

Re: How to Find Repeated case based on certain condition

P_Kale — Tue, 19 Mar 2024 09:32:33 GMT

Thanks for your help @Anil_Babu_Samineni

Re: How to Find Repeated case based on certain condition

P_Kale — Tue, 19 Mar 2024 09:34:45 GMT

Thanks @marcus_sommer for your help. Able to resolve not exactly as per above logic but I have used your part logic and build further on it which helps to resolve issue.