How would I write an expression for a slice t...

Nicholas_Christ · 04-05-2018 05:00 PM

How would I write an expression for a slice to return data where no records are found in a child table?

Example:

I have two tables Personas and Notas.

Multiple Notas can be associated to a Person.

But a Person may not have any Notas.

In SQL, I would write it this way:

Select *

from Personas left outer join Notas on Personas.Persona ID = Notas.Persona ID

where Notas.Persona ID is null

Levent_KULACOGL

@Nicholas_Christoffer You want to create a slice for the rows in Personas table where no Notas are associated for that row in Notas table, am I right?

Nicholas_Christ

Yes, that’s right

Levent_KULACOGL

Do you have a REF column between them?

Nicholas_Christ

Yes, Persona ID is PK on Personas and FK on Notas Translate

Levent_KULACOGL

@Nicholas_Christoffer I cannot understand from the screenshots which columns you have ref’d to the tables and what’s you sheet/column structure. So it’s hard to respond this way.

Bellave_Jayaram

In the Notas table, if you have a column called Related Personas, try putting this in Show_If, =COUNT([_THIS])>0

Nicholas_Christ

@Bellave_Jayaram @Levent_KULACOGLU

thanks for the support and suggestions.

I figured it out:

Step 1:

define a virtual column on Personas with the count of related Notas.

Use this expression to count how many Notas records are associated with this row:

count(select (Notas[Persona], [Persona]=[_thisrow].[Persona ID]))

Step 2:

define a slice of Personas with the condition that the result of this virtual column is 0:

[Notas Relacionadas]=0 Translate

Nicholas_Christ