Replicate the NOT IN Functionality of SQL in Alteryx.

[stj1120]

Hi all,

I need help in replicating the below SQL  Server AdventureWorks2012 database code into a Alteryx workflow. Here, I'm getting stuck at implementing 'NOT IN' functionality in Filter tool. I shouldn't input the SQL code into the Alteryx tool. I should completely make use of Alteryx tools which gives the similar SQL functionality. No Pre SQL or Post SQL statements should be used. 

Kindly help me out with the workflow for the SQL query. 

Select [ProductCategoryID] AS ProductID, [StandardCost], [Weight], [ListPrice],[StartDate],[UnitMeasureCode], [TransactionDate],

CASE WHEN DAY(PC.[ModifiedDate]) > 1 THEN CAST(CAST(YEAR(DATEADD(MONTH,4,PC.[ModifiedDate])) AS NVARCHAR) + '-' + CAST(MONTH(DATEADD(MONTH,4,PC.[ModifiedDate])) AS NVARCHAR) + '-01' AS DATE) ELSE DATEADD(MONTH,3,PC.[ModifiedDate]) END AS [DateStartedIncentive]

From [AdventureWorks2012].[Production].[Product] P

INNER JOIN [AdventureWorks2012].[Production].[ProductCategory] PC ON P.[ProductID] = PC.[ProductCategoryID]

LEFT JOIN [AdventureWorks2012].[Purchasing].[ProductVendor] PV ON PC.[ProductCategoryID] = PV.[ProductID]

LEFT JOIN [AdventureWorks2012].[Sales].[SalesOrderDetail] SOD ON PV.[ProductID] = SOD.[ProductID] LEFT JOIN [AdventureWorks2012].[Production].[TransactionHistory] TH ON SOD.[ProductID] = TH.[ProductID]

WHERE [Productid] + [SalesOrderID] + [ProductCategoryID] NOT IN

                                                         (Select [CustomerID] + [TerritoryID] +  [CustomerType]

FROM [AdventureWorks2012].[Sales].[Customer] WHERE DateADD(Month, -2, GETDATE()) AS ModifiedDate)

GROUP BY rowguid

Thanks in advance,

Best Regards,

Sreenivasa Teja.

[Dazzerman]

One way to approach what you want is to organise your SQL into two queries like the image below.

The Main query would be all of your SQL up to the last LEFT JOIN, and the Sub-Query would be everything within your NOT IN subquery.  This way, when you join the data from the two queries together, and only taking the results from the Left output from the Join, you exclude all the data from your subquery.  Then you can Summarise the data in accordance with your Group By statement, or Sort if that works better.

I hope this helps.

[stj1120]

Hi @Dazzerman 

Thank you for the quick response. I'd request you to please use only Alteryx tools for each SQL functionality and create the workflow and share it. SQL code shouldn't be inserted in the input tools. Please do the needful.

Thanks in advance,

Kind Regards,

Sreenivasa Teja.

[Dazzerman · Accepted solution]

Hi Sreenivasateja,

I have built a copy of the example workflow, attached, with Text Inputs to represent test data to represent your SQL data.  You can replace the Text Input tools with Input Data tools linked to your databases.  The Select tool may assist you with connecting to the new Input Data tool.

I have also provided both output layout options, Summarise and Sort, to demonstrate the different data layout that will result in case that helps you decide which one best suits what you want.

I hope this helps.

[stj1120]

Hi @Dazzerman , @MarqueeCrew , @NicoleJohnson 

Can you also please help me out with the Alteryx workflow for this SQL code? 

UPDATE HumanResources.Employee

  SET  

CurrentFlag = CASE  

  WHEN BusinessEntityID = '10' THEN 0 

  WHEN (SELECT CurrentFlag FROM HumanResources.Employee WHERE SalariedFlag = 1 AND BusinessEntityID = U.BusinessEntityID) = 1 OR (SELECT COUNT(*) FROM HumanResources.Employee WHERE CurrentFlag = 1 AND BusinessEntityID =    U.BusinessEntityID ) > 1  THEN 0 

  ELSE 1 END               

, SalariedFlag = 0 

  ,ModifiedDate = CAST(DATEADD(DAY,-2,GETDATE()) AS DATE) 

  ,StartDate = CAST(DATEADD(DAY,-DAY(DATEADD(MONTH,1,GETDATE())),DATEADD(MONTH,1,GETDATE())) AS DATE) 

  ,EndDate = CAST(DATEADD(DAY,-2,GETDATE()) AS DATE) 

  ,HireDate = GETDATE() 

  FROM  

  HumanResources.Employee U 

Awaiting your response. Thanking you!

Kind Regards,

Sreenivasa Teja.

[stj1120]

Please let me know how to replicate the above SQL code into Alteryx without inputting SQL code in Alteryx tools. 

Eagerly awaiting your responses, Thanks in advance.

Kind Regards,

Sreenivasa Teja.

[Dazzerman]

You might want to experiment with the Write Data in-DB Tool on some test data.

The Help page for this provides a couple of examples how different SQL queries can be used with it, and there are links to other In_DB tools in the menu.

https://help.alteryx.com/2019.2/LockInOutput.htm?TocPath=Tools|Tool%20Categories|In-Database|_____16

[stj1120]

Hi @Dazzerman ,

Will you please help me out with the workflow for the above Update SQL code?

Awaiting your response.

Kind regards,

Sreenivasa Teja.

[Dazzerman]

Hi Sreenivasa,

I don't have access to a sandpit database to play with at the moment via Alteryx, only a live system to report on that I would be able to use the In-DB tools on, so I'm not in a position to replicate code that would look similar to yours unfortunately.

However, in the first instance you will need to use a Connect In-DB tool, and you may even have permissions to put your full SQL code into that using the Query Builder to meet your needs.  Otherwise, you would need to pass the resulting data into a Write Data In-DB tool to do the update part of your code.  Good luck!