I have a case where I am using a Left Join on 2 tables and, as expected, the table on the right side of the Select has NULL values in columns that didn't match to the table on the Left side. Unfortunately each table contains a field which is necessary for filtering down the data and the Nulls are causing me problems.
The app is a comparison of Insurance Premiums to Insurance Claims. The issue arises when the user picks a date from the claims table -- this immediately filters down to only those clients who had a claim on that date. The problem is that this also filters down the list of Premium amounts to only those clients who had a claim (essentially an Inner Join), and that is undesirable. A client can pay their premium but not make a claim, and thus should still show up in the total of premiums.
I have tried every SQL trick I can think of; I've tried loosely coupled tables in QV; I've tried Left Joins from the SQL side as well as QV Loads with a Left Join. Nothing has worked.
The closest workaround I have is to substitute a value for the Null date that the user needs to select, but the client considers having to select the date they desire plus an additional value (that one substituted for the Null) unacceptable. They are dealing with hundreds of millions of dollars in premiums and claims and cannot risk the user forgetting to make 2 date selections to compare 1 period.
How can I get all data on the left to display and only the matching data on the right when the tables are linked and the user has made a selection on the right side?
I've attached a sample doc that may better illustrate.
While your explanation of the problem makes no sense to me, I can at least easily answer this:
Jim G. wrote:How can I get ALL clients to show up in the Insurance Premium straight table and only the Claim records linked to the chosen Valuation Date in the Claims Straight table?
If that's not what you meant, I won't be surprised, since I'm confused. But if it is what you meant, then cool, no need to further explain.
Thanks John. Unfortunately my client is not using version 8.5, but if set analysis is the only way to go, then this may be the excuse they need to upgrade. I appreciate your answer.
Using QV 8.1 (no Set Analysis capability), I have the exact same issue only I'm dealing with Registrations and Claims for recreational vehicles. The claims are associated to system and component codes (e.g. system=engine, component=piston). The fundamental problem is there are registrations that have no claims (same as your problem). When a user selects a system and/or component code, it selects/filters the claim data as well as the registration data; I do not want the registration data filtered (same as your Premiums).
Did you ever find a resolve to this issue (using QV 8.1)? Thanks
Believe I have similiar problem. Trying to report on sales Actions which are for $ Values and have a main filter of "Stage", but also include sales Events which are mostlyunrelated. Have to keep in same table as have common fields such as Plant, Date etc which want to filter across.
Problem is when user filters on a "Stage" which is common, all the Events become unavailable. I was using Set Analysis but becomes unwieldy due to number of fields related to Actions that have to be excluded.
Instead I've tried using the Star is *; function in the script. For the fields that aren't common, when building the combined table put the '*' value into that field. Usually only used this in security context, but appears to work in that it represents all possible values for that field. So when user filters on the "Stage" field (Action related) QV doesn't filter out all the Events, they stay available. But common fields with value such as Plant filtering works as usual.
Attached a quick sample of my method.
I did have some problems though if I do extra work on the table, eg.
LOAD *, 'XX' as Value2 RESIDENT Trans. It doesn't carry the Star logic through - have to explicitly restate it for every field each time.
Hope this helps.
I downloaded the .qvw file. When I double click on the file, I receive the error 'the document Sample_AllowStarFilteringWithDifferentDataSets.qvw failed to load'. What version of QlikView built the file? I am running 8.01.4760. Can you further explain? Thanks,
Other than the trick I initially employed using a substitue (empty string or whatever) to fill in missing/null values, I did not find another resolution using 8.1 I convinced my client to upgrade and that made life a whole lot easier thanks to set analysis.
Appreciate the response. Do you have any exampls of the set analsis you used. We have 9.1 on a test server to evaluate. I'm interested in knowning the effort using set analysis.