Tables synchronization

[francisco_cohen]

Hi,

I have 3 tables, and want to check the synchronization status between them.

[Source 1]:

LOAD

     Project Code,

     'Yes' as Table1

FROM file1.xlsx

[Source 2]:

LOAD

     Project Code,

     'Yes' as Table2

FROM file1.xlsx

[Source 3]:

LOAD

     Project Code,

     'Yes' as Table3

FROM file1.xlsx

My dashboard has the following table:

Project Code	Table1	Table2	Table3	Synchronized
Project 1	Yes	-	-	No
Project 2	-	Yes	-	No
Project 3	Yes	-	Yes	No
Project 4	Yes	Yes	Yes	Yes

I'm not being able to create the Synchronized dimension, it may be basic!

The best approach I've reached is:

Synchronized formula: if(len(Table1)+len(Table2)+len(Table3)=9,'Yes','No').

This works but when I try to filter my table by synchronized=No it oesn't work....

Any idea on how to solve this?

Thank you.

FC.

[marcus_sommer · Accepted solution]

You couldn't select the result of an expression else only dimensions. But you could create a calculated dimension from it - for example the following within a listbox by choosing expression instead of a field:

aggr(if(len(Table1)+len(Table2)+len(Table3)=9,'Yes','No'), [Project Code])

Otherwise you need to create this as a field within the script, maybe with something like this:

t1:

load distinct [Project Code], 1 as A from source1;

     join

load distinct [Project Code], 1 as B from source2;

     join

load distinct [Project Code], 1 as C from source3;

t2:

load [Project Code], if(rangesum(A,B,C) = 3, 'Yes', 'No') as Synchronized resident t1;

Depending on your requirements and your data might a similar approach with mapping more suitable: Mapping as an Alternative to Joining.

- Marcus