How to use group by deriving a custom field

[onkar1988]

Dear All,

I am working on a requirement, where in I want to derive a field in script using a group by on particular field. Let me try to explain using sample data.

1. For a specific process ID  P1, if the Process ID_status is 'In Progress' and for that Process ID if there is any entry for File_Received as 'N' then File status should be 'Not Started'.

2.  If Process ID_status is failed for any process ID then File status should be Failed irrespective of any File received.

Process ID	File_Received	Process ID_status	File Status
P1	Y	In Progress	Not started
P1	N		Not started
P2	Y	Failed	Failed
P2	N		Failed
P3	Y	Failed	Failed
P3	Y		Failed

Please let me know in case of any confusion/ query.

Regards,

Onkar Kulkarni

[Arthur_Fong · Accepted solution]

Try this:

Script:

Raw:
load * inline [
Process ID	File_Received	Process ID_status
P1	Y	In Progress
P1	N	
P2	Y	Failed
P2	N	 
P3	Y	Failed
P3	Y	
](delimiter is '	');

join

Data:
load *,
	if(wildmatch(Received,'*N*') and wildmatch(Status,'*In Progress*'),'Not Started',
		if(WildMatch(Status,'*Failed*'),'Failed')) as [File Status];
load [Process ID],
Concat(File_Received,[Process ID]) as Received,
Concat([Process ID_status],[Process ID]) as Status
resident Raw
group by [Process ID];

exit Script;

[Kushal_Chawda]

what should be the status if there is no 'N' for any process ID status?

[onkar1988]

If process ID Status is 'Failed' , File status should be 'Failed' irrespective of File received status.

[Kushal_Chawda]

What if Process ID status In progress and there is no 'N'?