Group by and Count script not working

[Parth_Gunjal]

Hello All,

1. I have 3 columns:
2.  < Source, Destination, [FTP List] >  { [FTP List] is a calculated field from previous script }
3. Column  [FTP List] has 2 entries: <ABSR,  FTP>
4. I want to count if [FTP List]='FTP' for a particular combination of (Source and Destination) 

What I have is:

Primary Key	Source	Destination	FTP List
1	A	C	ABSR
2	A	B	FTP
3	A	B	FTP
4	A	B	FTP
5	A	C	FTP
6	B	C	FTP
7	B	C	FTP

What I want is:

Source	Destination	FTPCount
A	B	3
A	C	1
B	C	2

I am trying to use the following code in Data Load Editor:

/*Code Starts*/

Grp_Source_Dest:
LOAD
Source,
Destination,
[FTP List],
count({<[FTP List] ={'FTP'}>} [FTP List]) AS FTPCount
RESIDENT Raw
Group By Source, Destination;

/*Code Ends*/

I have already loaded the data in table called 'Raw'
{ [FTP List] is a calculated field from previous script written in Raw}

But I am getting some error in the code (Kindly refer to attached Picture: 

Kindly let me know what am I doing wrong? Is there any other/better way to do it?
Thanks in advance!

[marcus_sommer · Accepted solution]

Try it in this way:

Grp_Source_Dest:
LOAD
Source,
Destination,
count([FTP List]) AS FTPCount
RESIDENT Raw where [FTP List] = 'FTP'
Group By Source, Destination;

- Marcus

[Parth_Gunjal]

Worked perfectly fine! Thanks Marcus 😀

Can you please tell me why my code was not eligible in the editor? Curious to know...
Also can I include Order by in this code to order by Descending FTPCount?
Thanks!

[marcus_sommer]

Your used condition is called set analysis and isn't available within the script else only within the UI.

On this step you couldn't order the results within the table because an applied order by statement would order the source respectively your resident-table. This means you would need an additionally load-step within the script - whereby do you really need this ordering? Within the UI objects are also possibilities to sort the order of the dimension-values.

- Marcus

[Parth_Gunjal]

Hey Marcus,
Thanks for a quick reply! 
No, I really don't require order by and have done that customization in UI itself, but I just wanted to know how logic works in this environment. Thanks for detailed explanations and suggestions. 
Regards,
Parth.