How to list records with the "NOT IN" filter based in two atributes in GoodData

Question

For example, I have these tables:

 __vendors___     _______sales_______
|V_Cod|Name  |   |S_Cod |Item  |Price|
|1    |Alan  |   |2     |apple |10   |
|2    |Fred  |   |2     |sugar |5    |
|3    |Alex  |   |3     |rice  |9    |
|4    |David |   |3     |meat  |20   |

I want to list all the sellers that don't have records in the sales table, but in the MAQL editor, the following metric don't seem to work:

select count(V_Cod) where V_Cod not in (S_Cod)

I also tried to make a count from each codes separately and then subtract one with the other, but it didn't worked (this metric is valid, but I need to see the name of the vendor in the report and the GoodData don't let me select the vendor_name in the HOW tab with this metric):

select (select count(V_Cod)) - (select count(S_Cod))

There is another way to make this count work?

Answer 1

This is working approach. You can use inner metric to count sales per vendor and use it together with IFNULL function. Then you can use such metric to filter the outer metric, which is just a simple count of vendors.

SELECT COUNT(V_Cod) WHERE (SELECT IFNULL(COUNT(S_Cod), 0) BY V_Cod)=0

It is currently necessary to use IFNULL because vendors who have no sales wouldn't otherwise be returned by the inner metric and thus the condition would not be satisfied.

This metric has dimensionality of vendor so you will be able to use V_Cod and Name in HOW.

Side note - from the example it does not seem that S_Cod is a connection point in Sales (more likely it looks like a reference to V_Cod). From a performance perspective, to calculate # of sales per vendor, it might be better to use COUNT(Records of sales) or COUNT(whatever is connection point of sales) rather than COUNT(S_Cod) because the engine can do simple count without distinct.

SELECT COUNT(V_Cod) WHERE (SELECT IFNULL(COUNT(Records of sales), 0) BY V_Cod)=0