2

This may be a long shot, so bear with me.

I have a query that returns a list of catagores (text strings) and a count of the number of times they occur:

Order Request,7842
Request,3976
Other,3247
...

Query:

SELECT Category__c, Count(Id) FROM Case WHERE CreatedDate = LAST_MONTH GROUP BY ROLLUP (Category__c)

It's working fine, however I need the data to be filtered more and currently have Perl script doing the job. I have a list of categories (about ~10 out of a total of ~16) of which the occurrences should be counted, and all occurrences of the other categories should dump into the "Other" category.

The script also organizes the final categories into a list in a specific order.

Is there any way to do this within the query rather than further processing the data with a script?


EX: The perl script takes data like the following:

Category1, 4
Category2, 6
Category3, 8
Category4, 2
Other, 7

And will re format it by combining certain categories into the "Other" category and ordering them in a certain way:

Category3, 8
Category1, 4
Other, 15
  • Can you maybe show the format the PERL script is outputting? I'm not entirely sure what you mean by "filtered more", but it sounds like post query processing and probably not something SOQL supports out of the box. – joshbirk Aug 5 '14 at 16:02
  • I went into a little more detail on the post, hopefully that helps – Ryan McClure Aug 5 '14 at 16:16
1

Not I can think of

There might be a way to get an ORDER BY working so that Other is always last (for example), possibly by adding a priority field or the like ... but it seems like from the output changes that you would be asking SOQL to perform some math based on business logic and I can't think of a way to get it to do that.

To put it another way, you are asking SOQL to get the count of Other in the system and that's what it will retrieve for you. If that number needs to change, that's logic/processing outside the query.

| improve this answer | |
  • That's what I figured, I was just hoping I would be able to remove some of the scripts from my project because it's becoming quite large. Oh well! – Ryan McClure Aug 5 '14 at 16:24
0

If your field is a picklist and you've ticked "sort it like that everywhere" it might be worth a shot adding ORDER BY Category__c to your query. To be honest I have no idea if it'll respect it ;) "sort in that order everywhere" is useful when you want to have a funnel chart component of Opportunity Stages - they really make sense to be sorted into a pipeline.

Another option would be to offload this mapping task on your friendly System Administrator ;) Check if Analytics API would work for your case (it has limits of returned rows for example) and if it does - build a report with nice bucket fields? They offer options to display unmapped values as other...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.