Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
onkar1988
Contributor III
Contributor III

How to use group by deriving a custom field

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 IDFile_ReceivedProcess ID_statusFile Status
P1YIn ProgressNot started
P1N Not started
P2YFailedFailed
P2N Failed
P3YFailedFailed
P3Y Failed

 

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

Regards,

Onkar Kulkarni

Labels (1)
1 Solution

Accepted Solutions
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

MC.PNG

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;

View solution in original post

4 Replies
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

MC.PNG

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
Contributor III
Contributor III
Author

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'?