SQL dataflow help

wgechter

Need some help from the SQL guru's out there. Table as seen below.

User Name User Org Client Name Client Org

Phil ABC Comp Jack DEB Comp

Phil ABC Comp Jack EFT Comp

Phil ABC Comp Jack ABC Comp

Phil ABC Comp Jack OKA Comp

Phil ABC Comp Jack IPA Comp

Phil ABC Comp Jill CDE Comp

Phil ABC Comp Jill XYZ Comp

The situation is Jack is the same client associated to many companies. If Jack is associated to the company that Phil is associated to, I don't want it to show up in my output of the data flow. In the above example. Jill would show up in my output because she is not associated to the company that Phil is associated to. Output as seen:

User Name User Org Client Name Client Org

Phil ABC Comp Jill CDE Comp

Phil ABC Comp Jill XYZ Comp

Thanks for the help, Wendi

Quick Links

Accepted answers

RGranada

I can't see why it should not work.

Try this version, it has minor changes:

Select * from tst_dj_clients_poc
WHERE `Client Name` NOT IN (SELECT DISTINCT `Client Name` From tst_dj_clients_poc WHERE `User Org`=`Client Org`)

Can you share a sample of your real data?

Regards,

All comments

RGranada

Hi,

Those are two rather similar ways of approaching your problem:

First approach:

**Select * from your_table_name
WHERE [Client Name] NOT IN (SELECT [Client Name] From your_table_name WHERE [User Org]=[Client Org])**

Second approach:

**SELECT a.* FROMyour_table_nameAS a INNER JOIN**

(SELECT [Client Name] FROM your_table_name WHERE ([User Org] = [Client Org])) AS b ON a.[Client Name] <> b.[Client Name]

These two queries are very similar in terms of performance, personally, I would go with the second one. They were tested in RedShift but should work in MySql with no problems.

Here's some great info on how to create a dataflow, in case you need:

http://knowledge.domo.com?cid=createsqldf

Hope this helps, don't hesitate to ask if you have any problems with this.

wgechter

Thanks Ricardo however it didn't work. The first approach gave me the results (using my example table): It still provided Jack as a client name only where Client Org didn't = User Org. I don't want to see Jack at all.

User Name User Org Client Name Client Org

Phil ABC Comp Jack DEB Comp

Phil ABC Comp Jack EFT Comp

Phil ABC Comp Jack OKA Comp

Phil ABC Comp Jack IPA Comp

Phil ABC Comp Jill CDE Comp

Phil ABC Comp Jill XYZ Comp

The 2nd approach did not run. It was running for over a day. I have 14 million rows in one dataset and 8 million rows in another dataset and so the dataflow would not work. Any other thoughts? Thanks, Wendi

RGranada

Hi @wgechter,

It's strange, the first approach runs for me. The following example table :

Using the following query :

Select * from tst_dj_clients_poc
WHERE `Client Name` NOT IN (SELECT `Client Name` From tst_dj_clients_poc WHERE `User Org`=`Client Org`)

Results in:

We should be missing something... I'm testing in DOMO with a MySql dataflow.

Basically what the query does is select all the records from the example table where the client name is not contained in the set of client names where the user org is the same as Client Org.

Please check your example table and query against mines. Tell me your conclusions, please.

Best regards.

wgechter

It may be that I have more columns than just the 4. I actually have several more about the client and about the user. This may be causing the discrepancy.

RGranada

Can you share the schema?

Regards.

wgechter

event date, event, user login, username, user org, client name, client dob, client number, client org, client org number.

The static fields here are; user login, username, user org, client name, client dob, client number. The rest of the fields may vary but if the client org = the user org I don't want the client displayed at all for any event. Does that make sense?

RGranada

I can't see why it should not work.

Try this version, it has minor changes:

Select * from tst_dj_clients_poc
WHERE `Client Name` NOT IN (SELECT DISTINCT `Client Name` From tst_dj_clients_poc WHERE `User Org`=`Client Org`)

Can you share a sample of your real data?

Regards,

wgechter

Actually this worked!! I was looking incorrectly at my data but this has worked! Such an easy query for the fix. Thank you so much, Wendi

other categories

Product Ideas
Have a Domo product enhancement idea? Submit or upvote on ideas in the Ideas Exchange.
Ideas Exchange
Suggest & vote on new features you would like to see implemented in the Domo Product.
Data Connections
Ask questions about Connectors, Workbench, Cloud Amplifier and get best practices from Domo peers
Connectors
Connectors, Custom Connectors, Writeback
Workbench
Ask questions about Workbench, a secure, client-side solution for uploading your on-premise data to Domo.
Cloud Integrations
Ask questions about Cloud Integrations and Federated Data connection to your data warehouse or lake.
Data & ETL
Ask questions about Magic ETL, SQL DataFlows, DataFusion, Dataset Views and get best practices from Domo peers
Magic ETL
Ask Magic ETL questions and get answers from Domo peers
SQL DataFlows
Ask SQL DataFlow questions and get answers from Domo peers
Datasets
Ask DataFusion and Dataset Views questions and get answers from Domo peers
Visualize & Apps
Ask questions about Beast Mode, Cards, Charting, Dashboards, Stories, Variables and get best practices from Domo peers
Dashboards
Ask Cards, Dashboards, and Stories questions and get answers from Domo peers
App Studio
Ask questions about building apps in App Studio.
Pro-code Components
Ask questions about pro-code components and Domo Bricks and get answers from Domo peers.
Charting & Analyzer
Ask questions about charting and Analyzer and get answers from Domo peers.
Calculations & Variables (Beast Mode)
Ask questions about using calculated fields and Variables (Beast Modes) in Analyzer.
AI & Data science
Ask questions about DomoAI and get answers from Domo peers.
Domo AI & AI Chat
Ask questions about AI Chat and AI assistants.
Managing AI
Ask questions about managing AI with AI Playground, AI projects, AI models, and more.
Jupyter Workspaces
Ask questions about Jupyter Workspaces, Notebooks, and file share.
Automate
Ask questions about App Framework, Workflows, Domo Bricks, Domo Developer, API and get best practices from Domo peers
Workflows
Ask questions about Task Center, building automations with Domo Workflows, and executing JavaScript or Python code with Code Engine.
Alerts
Ask questions about managing alerts in Domo and get answers from Domo peers.
Distribute
Ask questions about Domo Everywhere, Scheduled Reports, Mobile and get best practices from Domo peers
Domo Everywhere
Ask questions about embedded analytics with Domo Everywhere.
Reporting
Ask questions about Scheduled Reports, Report Builder, and Slideshow Publications.
Manage
Ask questions about Governance Administration, Approvals, Teams, Alerts, and Buzz and get best practices from Domo peers
Governance & Security
Ask questions about People, Groups, Roles, Sandbox, Activity log, Buzz, Teams, Approvals and PDP and get best practices from Domo peers
Navigation & Productivity
Ask questions about navigation, Projects & Tasks, Goals, and Buzz chat.
APIs
Ask APIs and Developer.domo.com questions and get answers from Domo peers
Add-ins & Plugins
Ask questions about plugins, Microsoft add-ins, and other third-party software integrations.
Domo Community Gallery
Watch how our Customers are using Domo to solve their complex problems.
Product Releases
Domo support and product teams are here to live-answer questions about the most recent product releases. Please post questions in this Forum board for all users to benefit (rather than submitting a support ticket).
Domo University
Questions or discussions related to Domo University, trainings and certifications
Community Forums
Getting Started
Welcome to Domo's Community Forums! You'll find everything you need to get started in this category.
Community Announcements
Get the latest from Domo's Community Team.
Social Groups
Archive
Old or outdated content that could still be found helpful.