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

expressions
(Nicholas Christoffersen) #1

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 KULAÇOĞLU) #2

@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 Christoffersen) #3

Yes, that’s right

(Levent KULAÇOĞLU) #4

Do you have a REF column between them?

(Nicholas Christoffersen) #5

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

(Levent KULAÇOĞLU) #6

@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) #7

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

(Nicholas Christoffersen) #8

@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 Christoffersen) #9

@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

(Bellave Jayaram) #10

@Nicholas_Christoffer , glad you figured it out👍