7 Replies Latest reply: Jan 28, 2014 12:36 PM by Krishnamoorthy Neeraj RSS

    Create a straight table out of disconnected tables in data model

    Krishnamoorthy Neeraj

      Hi All,

       

      Thanks for you time. Have simplified my data model in the example below for ease of explanation.I have three tables

       

      Table 1 : Has fields Name1, Sales1

      Table 2 : Has fields Name2, Sales2

       

      Due to the complexity of the data model Table 1 and 2 cannot be connected by the field 'Name' although 'Name1' and 'Name2' have very similar values

       

      Table 3 is an island that has only one field Name and it is not connected to any table.

       

      I am trying to create a straight table that has the following :

       

      1)  Dimension : Name

      2) Expression 1: Sum(Sales1) where Name1=Name

      3) Expression 2: Sum(Sales2) where Name2=Name

       

      I have tried using expressions such as Sum({$<Name1=p(Name)>}Sales1) but I cannot get the total corresponding the value of Name listed in the dimension column of the straight table.

       

      Also tried Sum({$<Name1={'$(=only(Name))'}>}Sales1) but shows up as an invalid expression. Any help regarding this would be great.

       

      Thanks,

      Neeraj