topic Re: How to create a flag based on record value in Script in App Development

How to create a flag based on record value in Script

Nisha_Pra — Wed, 03 May 2023 07:35:22 GMT

Hi All,

My data would look like below format. One case (caseID) will have multiple activities (CaseActivityID). Now I want to get the case count if any the case activities has L3 channel but need to consider only one record since its at the case level.

CASEID	CaseActivityID	RecordDate	Agent	Number of Ougoing Email	Channel	CallBackDate
1	21	01-Jan	a	4	L2	Null
1	22	02-Jan	b	4	L3	02-Jan
1	23	03-Jan	c	4	L3	Null
1	24	04-Jan	d	4	L2	04-Jan
1	25	05-Jan	e	4	L4	05-Jan
1	26	06-Jan	f	4	L3	06-Jan

Now I need to create IsChannel flag based on channel column and isCallBack column based on CallBack date. When one case has multiplecase activities , I need to look at any of these records are in channel L3. In that case, I need to consider only one record and rest of the records need to display as 0. How to achieve this in scripting

This is the output format I am looking for:

CASEID	CaseActivityID	RecordDate	Agent	Number of Ougoing Email	Channel	CallBackDate	IsChannel	IsCallBack
1	21	01-Jan	a	4	L2	Null	0	0
1	22	02-Jan	b	4	L3	02-Jan	1	1
1	23	03-Jan	c	4	L3	Null	0	0
1	24	04-Jan	d	4	L2	04-Jan	0	0
1	25	05-Jan	e	4	L4	05-Jan	0	0
1	26	06-Jan	f	4	L3	06-Jan	0	0

Re: How to create a flag based on record value in Script

Kushal_Chawda — Wed, 03 May 2023 09:46:24 GMT

@Nisha_Pra what is difference between both Flags? Looks same to me at a glance

Re: How to create a flag based on record value in Script

Nisha_Pra — Wed, 03 May 2023 11:41:06 GMT

Hi Kushal, Channel flag needs to be calculated based on channel column for each case.. IsCallback column needs to be calculated based on call back date for each case..

Hope it helps

Re: How to create a flag based on record value in Script

Kushal_Chawda — Wed, 03 May 2023 11:46:57 GMT

@Nisha_Pra for both of this flag, channel ='L3' condition is common? Below is my understanding , let me know if it is correct

IsChannel -> Check 'L3'->Pick first record based on first RecordDate

IsCallBack> Check 'L3'->Pick first record based on first CallBackDate

Re: How to create a flag based on record value in Script

Nisha_Pra — Wed, 03 May 2023 11:52:07 GMT

For the first Activity which has Channel value is like %L3%; populate the flag IsChannel == 1 and populate 0 for rest all activities. This is considered assuming there could be multiple activities for each case which can have Channel value as %L3% but we need to flag only the 1st Activity which has channel value as %L3%.

IsCallBackCase -

For the first Activity which has Callback date value, populate the flag IsCallBackCase == 1 and populate 0 for rest all activities. This is considered assuming there could be multiple activities which can have callback date but we need to flag only the 1st Activity which has call back date.

Re: How to create a flag based on record value in Script

Kushal_Chawda — Wed, 03 May 2023 12:42:38 GMT

@Nisha_Pra try below. Make sure that your RecordDate and CallBackDate are in proper date format, if not then first formate it using Date#() function in first load of data then try subsequent load as below

Data: load * Inline [ CASEID CaseActivityID RecordDate Agent Number of Ougoing Email Channel CallBackDate 1 21 01-Jan a 4 L2 1 23 03-Jan c 4 L3 1 22 02-Jan b 4 L3 02-Jan 1 22 02-Jan b 4 L3 02-Jan 1 24 04-Jan d 4 L2 04-Jan 1 25 05-Jan e 4 L4 05-Jan 1 26 06-Jan f 4 L3 06-Jan ](delimiter is '\t'); Left Join(Data) Load CASEID, date(Min(CallBackDate)) as CallBackDate, 1 as IsCallback_temp Resident Data Group by CASEID; Left Join(Data) Load CASEID, date(Min(RecordDate)) as RecordDate, 'L3' as Channel, 1 as IsChannel_temp Resident Data where Channel='L3' Group by CASEID; Final: Load *, if(IsNull(IsChannel_temp) or Previous(IsChannel_temp)=1,0,IsChannel_temp) as IsChannel, if(IsNull(IsCallback_temp) or Previous(IsCallback_temp)=1,0,IsCallback_temp) as IsCallback Resident Data Order by CASEID,RecordDate; Drop Table Data; Drop Fields IsChannel_temp,IsCallback_temp;

Re: How to create a flag based on record value in Script

_Iswarya_ — Thu, 04 May 2023 08:18:37 GMT

Hi @Nisha_Pra ,

Please find the script below:

Temp:
Load * Inline [
CASEID, CaseActivityID, RecordDate, Agent, Number of Ougoing Email, Channel, CallBackDate
1, 21, 01-Jan, a, 4, L2,
1, 22, 02-Jan, b, 4, L3, 02-Jan
1, 23, 03-Jan, c, 4, L3,
1, 24, 04-Jan, d, 4, L2, 04-Jan
1, 25, 05-Jan, e, 4, L4, 05-Jan
1, 26, 06-Jan, f, 4, L3, 06-Jan
];

left join(Temp)
Ischannel:
Load
min(CaseActivityID) AS CaseActivityID,
'1' as IsChannel_Temp
resident Temp
where Channel='L3'
Group by CASEID;

left join(Temp)
IsCallBackCase:
Load
min(CaseActivityID) AS CaseActivityID,
'1' as IsCallBackCase_Temp
resident Temp
where len(CallBackDate)>0
Group by CASEID;

Final:
Load *,
if(isnull(IsChannel_Temp),0,IsChannel_Temp) as IsChannel,
if(isnull(IsCallBackCase_Temp),0,IsCallBackCase_Temp) as IsCallBackCase
Resident Temp;
drop Table Temp;

Drop fields IsChannel_Temp,IsCallBackCase_Temp;