topic Re: JOIN question in QlikView

JOIN question

Mon, 03 Nov 2014 22:37:41 GMT

I have a table (EVENTS) that has OwnerID as the primary key, and in which there are multiple records for each OwnerId. I also have a table called TARGETs, which has the same primary key, but has a 1:1 relationship (one row for each sales rep).

There are some sales reps in the TARGETS table that have no events. However, want these sales reps to be included in the same table as the reps who DO have events. For example:

SALES REP TARGET EVENTS

Rep A 100 50

Rep B 50 0

Rep C 75 25

If I select an event date range for a rep who doesn’t have any events in that range, I still want to see them in the table with their target.

I’ve already done two joins to get the EVENTS table, and two separate joins to get the TARGETS table. How do I join these two tables to get ALL records included?

Thanks!

Re: JOIN question

maxgro — Mon, 03 Nov 2014 22:44:08 GMT

could you post your qlik doc?

Re: JOIN question

JonnyPoole — Mon, 03 Nov 2014 22:50:27 GMT

To me this is more of a UI issue that can be resolved with a SET ANALYSIS expression to ignore the date filter on the TARGETS expression

If Events expression is this: sum( Events)

Then Targets expression could be something like this: sum( {$<Date=>} Targets)

...where Date is the filter for the eventdate. This will ensure Targets ignores the date filter but 'Events' does not.