Replicating the CASE Statement in Alteryx which has Select Query it with the WHERE clause

[stj1120]

SQL CODEAlteryx Workflow.Hi all,

Kindly help me out with the below SQL code by replicating into Alteryx tools. I tried in Alteryx but getting confused how to replicate the CASE statement in Alteryx. 

If you see the SQL Code screen shot,  there the CASE statement has a Select query inside it with the WHERE clause. That is where I'm unable to understand what and how to write the logic in the Formula tool in Alteryx to replicate the same result as 0 and 1. 

For reference the SQL Query is: 

Database: AdventureWorks2012.

select distinct
case
when isnull((select count(PersonType) from AdventureWorks2012.Person.Person where BusinessEntityID = P.BusinessEntityID
AND ModifiedDate = P.ModifiedDate AND PersonType <> 'SC'),0) > 0 then 1
else 0
end as [SCPerformed]
FROM AdventureWorks2012.Person.Person P

It'd be really grateful if anyone can help me out on this regard asap. 

Awaiting your responses. 

Thanks in advance.

Kind Regards,

Sreenivasa Teja.

[GiuseppeC]

Hi @stj1120,

it'd be good to understand why you need the value 0/1 and how you'd use them downstream.

If you just remove the Formula tool (right click on it > 'Delete and Connect around'), the Summarize tool will give you a table with some rows (corresponding to your 'when isnull(...)>0' case) or an empty table (your 'else' branch of the case).

Then you can add a Count record tool after the Summarize, which will give you 0 in the case of an empty table, or the number of rows. Then you can use a Formula after the Count Record with a standard IF [Count]>0 THEN 1 ELSE 0 ENDIF statement.

[stj1120]

Hi GiuseppeC

Could you please be more specific and explain me in more detail. It'd be great if you can attach a workflow if you have worked on this.
Awaiting your response.

Thanks in advance.

Kind regards,
Sreenivasa Teja.

[stj1120]

I'm getting confused how to write the logic of Case statement using PersonType column and other columns that are specified in WHERE clause in Formula tool.

[GiuseppeC · Accepted solution]

Hi @stj1120,

see below and attached, I have appended comments to the workflow so that you can link the tools to your SQL statements.

Hope this helps!

Giuseppe

[stj1120]

Hi @GiuseppeC 

Kindly can you also help me out on how do you replicate this below sql code?

select

(select count(*) from AdventureWorks2012.HumanResources.Employee where CurrentFlag = 1 AND LoginID = E.LoginID)

from AdventureWorks2012.HumanResources.Employee E

Awaiting your response. 

Kind Regards,

Sreenivasa Teja.

[GiuseppeC]

Hi @stj1120,

I'm not a SQL expert, can you please clarify what your statement is supposed to do?

From what I understand, the inner select statement returns the number of employees that respect the where clause, say number. Then, the outer 'select number from Employee E' should just return one column with number replicated for as.many rows as you have in the Employee table?

Is this what you want? 

Giuseppe

[Dazzerman · Accepted solution]

Hi @stj1120 ,

The tools to satisfy the second part of your question would also be very similar to the example that @GiuseppeC gave you.

Maybe if you upload the workflow you're working on, and replace the connection to the database with a Text Input tool containing an extract of the data you're working on, one of us could recommend changes to overcome whatever the issue is that you're experiencing.