Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Parth_Gunjal
Contributor II
Contributor II

Group by and Count script not working

 

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 KeySourceDestinationFTP List
1ACABSR
2ABFTP
3ABFTP
4ABFTP
5ACFTP
6BCFTP
7BCFTP

What I want is:

SourceDestinationFTPCount
AB3
AC1
BC2

 

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: 
Annotation.png

 

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

Labels (3)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

4 Replies
marcus_sommer

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

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

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.