1 Reply Latest reply: Sep 7, 2017 3:11 AM by Marcus Sommer RSS

    Tables synchronization

    Francisco Cohen



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


      [Source 1]:


           Project Code,

           'Yes' as Table1

      FROM file1.xlsx


      [Source 2]:


           Project Code,

           'Yes' as Table2

      FROM file1.xlsx


      [Source 3]:


           Project Code,

           'Yes' as Table3

      FROM file1.xlsx


      My dashboard has the following table:

      Project CodeTable1Table2Table3Synchronized
      Project 1Yes--No
      Project 2-Yes-No
      Project 3Yes-YesNo
      Project 4YesYesYesYes



      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.


        • Re: Tables synchronization
          Marcus Sommer

          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:



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


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


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



          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