Solved

Specific SQL keyword

  • 2 February 2023
  • 5 replies
  • 65 views

Hi,

 

Is there a a way to do a MINUS/EXCEPT like in SQL ?

Objective is to count all users that are having event for something but not for something else.

example :

    SELECT DISTINCT LEARNER from ...
    WHERE USER_ACTION_FACT_TYPE  in ('Bookmark', 'Share', 'Comment', 'Like','ArticleView'  )
    MINUS
    SELECT DISTINCT LEARNER from ...
    WHERE USER_ACTION_FACT_TYPE in ('Training Session' )

 

What about INTERSECT ?

    SELECT DISTINCT LEARNER from ...
    WHERE USER_ACTION_FACT_TYPE  in ('Bookmark', 'Share', 'Comment', 'Like','ArticleView'  )
    INTERSECT
    SELECT DISTINCT LEARNER from ...
    WHERE USER_ACTION_FACT_TYPE in ('Training Session' )

 

Best regards

Jean-Christophe 

icon

Best answer by Joseph Heun 21 February 2023, 15:22

View original

5 replies

Userlevel 2

Hello Jean-Christophe,

 

The first case could be easily solved by “NOT IN” clause. Something like:

SELECT Learner WHERE USER_ACTION_FACT_TYPE  in ('Bookmark', 'Share', 'Comment', 'Like','ArticleView'  ) AND NOT in ('Training Session' )

Or you could easily filter out the values from the particular attribute later on when creating insight or report. 

 

Regarding the second case, unfortunately I cannot see any way how to achieve this via MAQL. That might be something that should be done on your end before loading the data into our platform. 

 

Feel free to check the bellow article for more information about SQL vs. MAQL:

https://help.gooddata.com/doc/enterprise/en/dashboards-and-insights/maql-analytical-query-language/maql-versus-sql/

 

 

 

 

 

 

Thanks Julius,

 

Unfortunately it’s not giving what’s expected. What we try to achieve here is really a MINUS, which means we don’t want to count users that are in ('Bookmark', 'Share', 'Comment', 'Like','ArticleView'  )  even if they are in in ('Training Session' ).

 

Objective is to build an insight with :

  • Total of users that are only in (‘Training Session')
  • Total of users that are only in ('Bookmark', 'Share', 'Comment', 'Like','ArticleView' )
  • Total of users that are in both

So we have 100% of users at the end.

 

Best regards

Jean-Christophe 

Userlevel 2

Hello Jean-Christophe,

 

Thanks for your reply. 

 

Just for the clarification - should the MINUS mean in this case that you are for example trying to return only particular unique Learners which are present in (Bookmark, Share, Comment, Like,ArticleView) but excluding the same ones which are present in (Training Session)? To get rid of “duplicates” ones?

 

Or is the question really related only to COUNT of the total users/learners as you have suggested above? If thats the case, the bellow should work:

 

In MAQL, you can subtract metrics (please see Arithmetic Operations). This can solve the first part of your question. For example you can create two metrics:

1st_Metric:

SELECT COUNT(Learner) WHERE USER_ACTION_FACT_TYPE  in (Bookmark, Share, Comment, Like,ArticleView ) 

2nd_Metric:

SELECT COUNT (Learner) WHERE USER_ACTION_FACT_TYPE  in (Training Session)

And then subtract them within another metric:

SELECT  1st_Metric - 2nd_Metric

 

Regarding the other part of the question - Again, this can be definitely achieved by separated metrics already used above:

  • Total of users that are only in (‘Training Session'):
    SELECT COUNT (Learner) WHERE USER_ACTION_FACT_TYPE  in (Training Session)
     
  • Total of users that are only in ('Bookmark', 'Share', 'Comment', 'Like','ArticleView' ):
    SELECT COUNT(Learner) WHERE USER_ACTION_FACT_TYPE  in (Bookmark, Share, Comment, Like,ArticleView )
     
  • Total of users that are in both:
    SELECT COUNT(Learner) WHERE USER_ACTION_FACT_TYPE  in (Bookmark, Share, Comment, Like,ArticleView, Training Session)

 

However, if this is not what you are looking for exactly, could you please elaborate a bit more in regards how is your LDM structured and also provide us with some particular example use-case and example of the result you would like to achieve? 

 

Thanks!

Hi Julius,

Here is what we try to achieve for example here  :

Counting the number of users in each category. If a user is having a record for January in Session and Communication he should be counted as a “Both” facts user, if he is only having communication he should be counted as communication only.

 

 

Userlevel 3

Hello, This is possible through the use of the WHERE Clause and other filters previously mentioned. You can also use ‘NOT’ ‘OR’ or, ‘AND’ to combine filters in your case. 

 

There is a great free tutorial on creating these metrics with MAQL at our GoodData University. If you are still struggling to build the report, please open a ticket with our support team with a link to the report in question. 

Reply