just use the same expression in the right chart and everything will work right
the engine connects the table as full outer join
but what you did in the right chart
you counted the text field , so the result you got is right , because the table aren't joined together in the script
so each text appears once in the model , because the engine connected the tables
you can use dimension from any table with measure from different table , as long as they are connected
I just read Joins and Lookups
It did not alleviate my fears about Qlik's mysteriousness:
The association can be evaluated to a left join or a right join depending on where the user has made a selection. And with no selection, the association is always evaluated to a full outer join.
Anyway, this part seems to explain what is happening here:
If the aggregation is a simple Sum(X), then QlikView will loop over the records of the table where the
field X is found, summing X. But if the expression is Sum (X*Y) and X and Y are found in different tables, then QlikView has to perform the join in memory generating a virtual table over which the summation will be made.
The dimension and measure I'm using in the second pie chart are both available in the reasonComplete table, so QS doesn't bother joining it to countLog. It just looks at the tiny reasonComplete table and heads to the beach to relax. If I could tell QS that what I care about is the reasonID from countLog, it'd probably work, but I don't see any way to do that from the worksheet. I tried this, but it's invalid:
Is there some other way to force it to take countLog into account?
EDIT: yes, there is a way and it's easy!
Just have it count a field that's in countLog but NOT reasonComplete:
The field used is irrelevant as long as it's in the table you want to join to and not in the same table as the dimension.
END OF EDIT
Otherwise, that article leads me to believe that these are my options, none of which is particularly appealing:
- Join the tables using the source DB prior to Qlik data load
- Join the tables using the source DB during Qlik data load
- Join the tables with Qlik during data load
- Join the tables with Qlik during data load, but use the Keep prefix so that the tables are joined on demand instead of ahead of time. This probably wouldn't even help, because just like in the current situation, Qlik probably wouldn't see a reason to trigger the join.
- Use apply_map() or lookup() during Qlik data load to replace reasonID with reason. I don't know how this works under the hood, so I don't know how bad the memory penalty is (reason is way bigger than reasonID). It also only gets you one field from the lookup table instead of all of them.
Anyone have any guidance?