How to create a flag based on record value in Script

[Nisha_Pra]

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

[Kushal_Chawda]

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

[Nisha_Pra]

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

[Kushal_Chawda]

@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

[Nisha_Pra]

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.

[Kushal_Chawda]

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

[_Iswarya_]

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;