Case statement is throwing error in formula in-db tool

[Vikas038]

Hi Team,

I am using simple case statement via formula tool in In-DB. Formula is below. 

count(distinct(case when lower(documenttype)='credit opinion - translation' then Login end))

and before this formula i have all values(via summarization) that i want to keep as group by 

but when i run the application on gallery it is throwing below error. Please help me on this that how can i overcome this issue.

/* Error SQLPrepare: [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Athena Error No: 912, HTTP Response Code: 8528, Error Message: SYNTAX_ERROR: line 325:2856: '"doc_id"' must be an aggregate expression or appear in GROUP BY clause [Execution ID: d197ddf8-9d5e-4ca6-b014-48685e335fa5] (Tool Id: 33)*/

I have also attached the screen shot of application. Also, i am sitting on paid license of Alteryx and in process of delivering high priority projects to management from last one month. So your quick response will be highly appreciated that can resolve this issue

[jrgo · Accepted solution]

@Vikas038 

Issue is the expression you're trying to pass. The In-DB Formula tool should only be:

case when lower(documenttype)='credit opinion - translation' then Login end

After, use a Summarize In-DB tool to do your distinct count.

[Vikas038]

I really appreciate your quick response and workable solution. Although, i will test the result further. Let me know any common platform where i can share your feedback.

But, i also need your inputs on below points.

1. After making suggested changes, application`s performance has been affected. i am attaching screen shot for complete application. please let me know what changes i can make to improve performance

2. I am running this analysis to have some documents in Japanese language. But alteryx convert document names and placed some bad special characters with name. How can i handle this situation? Eg is 

/* [MJKK] :  */

/*while instead of above name, documents real name is [MJKK] 静岡県: 年次アップデート */