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
-
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.
-
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
-
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?-
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.
-
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.
-
please make sure that any data you upload does NOT contain any private information.
-
please find the sample for my request. ST Acc relation with Name on other 2 table
-
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
-
Thanks, Got it. only from Model we can Achieve it. Thanks For your Help.
-
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.
-
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
-
-
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