Filtering pivot table in Sisense with negation

Question

I'm trying to implement a simple filter in Sisense, but I can't figure it out.

My data is stored in a PostgresQL DB. A minimal example looks something like this:

CREATE TABLE fact_table (
    first_name text,
    salary integer
);

INSERT INTO fact_table VALUES ('John', 100), ('Jack', 200), ('Mary', 300), ('Klaus', 400);

CREATE TABLE dim_table (
    first_name text,
    last_name text    
);

INSERT INTO dim_table VALUES ('John', 'Smith'), ('Jack', 'Smith'), ('John', 'Jackson'), ('Mary', 'Smith'), ('Klaus', 'Jackson');

I want to create a pivot table with the first_name in the rows and salary as values in Sisense.

Now there are two scenarios I want to filter for:

1) Select all first_names, for which someone with the last name 'Smith' exists

=> easy in Sisense, just create a relationship on first_name <-> first_name and filter on 'Smith'

Our result set is {Jack, John, Mary} and their respective salary.

2) Select all first_names, which are not shared by someone with the last name 'Smith'. This is the negation of 1). Our expected result set {Klaus}.

I don't know how to do this. It's trivial in SQL: SELECT * FROM fact_table WHERE first_name NOT IN (SELECT first_name FROM dim_table WHERE last_name = 'Smith');

I haven't found any suitable option in Sisense. If I create a list filter and deselect Smith, I get all non-Smiths from my dim_table, which is not the same logically (and returns John instead of Klaus).

I must be missing something obvious. Any ideas?

Thanks a lot!

Answer 1

I think both your 1st and 2nd scenario of filter can be achieved by the custom SQL in the elasticube. You need to do a left join on both tables and get every row from fact. In your filter of first_name then you can deselect first_name as "Smith" but select NULL values to show "Klaus"