0

Is it possible to create a combined list by selecting members from two different tables and columns?

Is it possible to create a combined list by selecting members from two different tables and columns?

In formula creation, I'm getting the following message

"Sets must use members from the same table and column."
 

12 replies

null
    • Customer Solutions Architect
    • Moshe_Yossef
    • 2 wk ago
    • Reported - view

    Hi

    A list is always designated to a specific table and column /hierarchy.

    Can you perhaps explain what you are trying to achieve? I'm confident we can achieve the required functionality.

    • Lead Software Engineer
    • Vijayan_Krishnan
    • 2 wk ago
    • Reported - view

    Example : SELECT A.number1, B.description, C.description FROM TableA A JOIN TableB B ON A.number1 = B.number1 JOIN TableC C ON A.number1 = C.number1;. 

    I'd like to load only the descriptions in the list that match from both tables based on the values from Table A

    • Customer Solutions Architect
    • Moshe_Yossef
    • 2 wk ago
    • Reported - view

    Can explain what your model looks like and what you're trying to do? Do you want one list that will have all the values from B.Description and C.Description? what will you do with it consequently?

    You could create a list of all B.Description and then  filter exists on all C.Description. So you'll get only ones that exist in both dimension - but I sense I don't have the actual use case.

    Can you share on your model structure and the functionality you need?

      • Lead Software Engineer
      • Vijayan_Krishnan
      • 2 wk ago
      • Reported - view

       

      In Table A, I have 10 records. From those, I've selected only 4 records with values b1, b2, b3, and c1. Among these, b1, b2, and b3 have foreign key relations with Table B, while c1 has a foreign key relation with Table C. Now, I need these matching 4 descriptions compiled into one list.

      • NPANS
      • 2 wk ago
      • Reported - view

        If you need the details resolved at the grain (which is sounds like), you need to use custom columns. Otherwise, you can build a semantic "list" and filter it using the nonempty function. 

      Your explanations are still vague.  So, if you want specific help, please upload 3 small representative CSV files of each table and then show us the outcome you would like to see - mocked up in a grid image perhaps. 

      • Let me connect you
      • Community_chris
      • 2 wk ago
      • Reported - view

        please make sure that any data you upload does NOT contain any private information.

      • Lead Software Engineer
      • Vijayan_Krishnan
      • 2 wk ago
      • Reported - view

       

      please find the sample for my request. ST Acc relation with Name on other 2 table

      • Senior Director of Product
      • Ian_Macdonald
      • 13 days ago
      • Reported - view

       

      Here is one solution.

      In your model, it requires that your JOINS be of OUTER type from ST Acc to the NA and FA tables:

      Then create a "Custom Column" through the Formulate module:

       

      The code is

      CASE(
           [ST Acc].[Account]="701",[FA].[Description],
           [ST Acc].[Account]="800",[FA].[Description],
           [ST Acc].[Account]="810",[FA].[Description],
           [ST Acc].[Account]="100",[NA].[Description]
      )

      Then place the Key from Table A and the Custom Column in the Rows:

      Hope that helps.

      Ian

      • Lead Software Engineer
      • Vijayan_Krishnan
      • 13 days ago
      • Reported - view

        Thanks, Got it. only from Model we can Achieve it. Thanks For your Help.

      • NPANS
      • 13 days ago
      • Reported - view

       The key trick lies in the left joins - a central concept resolved in SQL itself.

      Custom Columns don't have to be in the model. You can add the columns into the model itself as a "virtual column" or as a "custom column" independently.

      • Senior Director of Product
      • Ian_Macdonald
      • 13 days ago
      • Reported - view

       

      Well, no, you can create a Custom Column through Formulate outside of Model. 

      BUT the JOINS must be of type LEFT OUTER. If you try the same query in SQL, it will only work with LEFT OUTER JOINS favouring Table A. So it is model dependant in terms of the JOINs but the calculation has to be done through Formulate as Virtual Columns in Model can only reference their parent table.

      Ian

    • Senior Director of Product
    • Ian_Macdonald
    • 2 wk ago
    • Reported - view

    It is important to remember that the Pyramid Semantic models that represent your underlying relational data are fully dimensional in nature, i.e., each attribute is treated independently of all the others. Thus in Pyramid Lists, the members of the list are referring to the list of unique values that make up that attribute. There is no concept of the JOIN construct you are referring to when manipulating Lists in Pyramid.

    When using Pyramid semantic models, then Lists can only contain elements from the same Column / Table or, in Pyramid terms, Hierarchy / Dimension. This how the Pyramid expression language, PQL has been designed. Note however, that if you are working with SQLServer Analysis Services semantic models, using MDX, this does support Sets from multiple hierarchies.

    As Moshe says, we'd need to know a bit more about your model and final output before we can help further.

    Hope that helps.

    Ian 

Content aside

  • Status Answered
  • 13 days agoLast active
  • 12Replies
  • 54Views
  • 5 Following