Using Dataiku
- Hi, while doing a normal join on one table I run into this error "in act.compute_Base_Modelos_1_1_1_NP: Column name can not contain comma, quotation mark, leading or trailing white spaces with DSS eng…
Last answer by cullimkSome experimentation reveals that the problem I had is a result of having columns that are arrays on the input datasets (even changing the storage or meaning did not help), even if you do not include them in the join output. Once eliminated and/or transformed the arrays in a prep step prior to the join, the join worked. Thanks for the speedy reply!
- Hi, I have a dataset where some combination of columns results in a unique key: 1 A 1 B 2 B 2 C 3 A The first column with numerical values represents users and the second column with characters are th…
Solution by Alex_CombessieHi,
What you describe is often described as the "ANTI JOIN" concept in SQL. I would recommend indeed to accomplish this using a SQL script recipe in Dataiku.
Unfortunately, there is not a common syntax for ANTI JOINs in SQL, as for regular LEFT/RIGHT/INNER/... JOINS. Depending on the flavour of SQL database you are using, you can look up the specific syntax you need. For instance, in PostgreSQL you can use a syntax like this:
SELECT *
FROM actor a
WHERE NOT EXISTS (
SELECT * FROM film_actor fa
WHERE a.actor_id = fa.actor_id
)This is documented online in this article: https://blog.jooq.org/2017/01/12/a-probably-incomplete-comprehensive-guide-to-the-many-different-ways-to-join-tables-in-sql/. For other SQL flavours, similar articles certainly exist to help you write the right script.
Hope it helps,
Alex