Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
"so that I can have all data for Activities even when they have nulls in table1." Are you missing the null activities? Any chance you unchecked "Include Null Values" in the measure?
The issue might be because you're using a right join, which retains all records from the right table (fact_table2) and only matches records from the left table (fact_table1). Since fact_table1 is your main fact table containing data about activities that have happened, it's possible that some records from fact_table2 are not present in fact_table1, leading to partial or unexpected results.
Hello everyone,
@BrunPierre but the thing is I wanted to have data for every activity (even it hasn't happened yet) along with the details for those who happened, so a right join would solve this for me - every activity_id, with nulls if it hasn't happened and all the info matched if it indeed has. Am I grasping something the wrong way about right join?
They were partial in a very weird way, not even retaining all info from fact_table2... weird things. The cardinality shouldn't be a problem right?
Based on your description, it appears that the result would resemble that of an "OUTER JOIN." The final table combines data from both tables based on a related field, while including all records, even if there is no match.
What you are doing seems to be suitable to your description. This means if it's not working your data are different to your expectation, for example if the relationship isn't 1:n else n:m and/or the fact_table2 has not all keys of the fact-table1 and/or there are issues with the data-quality of your keys respectively to the way they are created.
If the combined ID fields contain numbers you may get invalide ones by applying:
ID1 & ID2 as Key
Better would be to add a delimiter between them, like:
ID1 & '|' & ID2 as Key
@marcus_sommer I will certainly try it, because yes, I believe they're ints.
In fact another thing I tried before was to only right join fact_table2 with fact_table1 on field Activity_id, but guess what, data was off, then I created Activity_Key to get all possible combinations for common fields of both tables and then right joined, but only my solution with link table worked. This is a first-timer.
I'm pretty sure the cardinality is 1 to many between my fact tables.
I think if you investigate the missing and/or wrong data more closely you should detect why it happens - just picking one or two and then fixing the cause and then going to the next - because there might be several different reasons.
This shouldn't be done with any charts else with table-boxes and very helpful might be also to add recno() and rowno() in both loads and naming them differently. With it you could track each record. Sometimes it's also useful to load the origin tables additionally as independent tables, like:
qualify *; t1: load *, recno() as RecNo, rowno()as RowNo from fact1; unqualify *;
qualify *; t2: load *, recno() as RecNo, rowno()as RowNo from fact2; unqualify *;
which enables a direct comparing of key-values from the sources as well as from the target.
In each case take a closer look on the above mentioned invalide key-creation.
Personally I wouldn't use either a join nor a link-table else I would use a mapping to merge the information from both facts, for example:
@marcus_sommer , wow such a cool bunch of ideas to try and to think about.
Regarding your last proposed solution, I'm only confused as per why do I need Event_id as a column in my Activity table when all I want to have is activities. Did you do that in order to join with fact1 on event_id after this?
I just took the fields from your table1 and the right join should be added them, too. And it was also a demonstration to combine multiple returns and how to split them again with a subfield(). If you don't need this field just skip it.