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

    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 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.

      FC.

        • 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:

           

          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