Problem Note 50348: A Map node returns incorrect counts if the underlying tables are connected with an outer join
Incorrect counts might be returned when you execute a SAS® Customer Intelligence campaign. This problem occurs if the diagram contains a Map node that is refining its output on data in tables that are connected with an outer join.
For example, you have an information map that has at least two subjects, such as Customer and
Accounts.
- Each customer may have 1 or many contracts.
- Each contract may have 0-many events. The join to the event table must be an outer join to enable selection of customers or contracts with no events.
- Create an information map with the following relationship between the underlying tables:
- join between Contract and Product_Purchase(Event) a left outer join
- join between Product_Purchase and Product a left outer join
Build the campaign based on information map above.
- Select all customers with no products.
- Connect the Select/Multi-select node to a Map node. Map to Accounts and select Refine.
- Specify the refine limit as 1 contract. Specify the refine selection as Product
is missing.
- Run the Map node. Note that 0 contacts are selected.
When you review the audit log for the Selection node, you see outer joins between Contract and
Product_Purchase(Event) and between Product_Purchase(Event) and Product.
The audit log for the Map node reports those same joins as inner joins, which is incorrect and explains the zero count.
There is no workaround.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
SAS System | SAS Customer Intelligence Studio | Microsoft Windows Server 2003 Datacenter Edition | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Microsoft® Windows® for x64 | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Microsoft Windows Server 2008 | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Microsoft Windows Server 2003 Enterprise Edition | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Microsoft Windows Server 2003 Standard Edition | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Microsoft Windows Server 2003 for x64 | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Microsoft Windows Server 2008 R2 for x64 | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Microsoft Windows Server 2008 for x64 | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Microsoft Windows XP Professional | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Windows 7 Enterprise 32 bit | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Windows 7 Enterprise x64 | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Windows 7 Home Premium 32 bit | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Windows 7 Home Premium x64 | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Windows 7 Professional 32 bit | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Windows 7 Professional x64 | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Windows 7 Ultimate 32 bit | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Windows 7 Ultimate x64 | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Windows Vista | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
Windows Vista for x64 | 5.41 | 6.1_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
Type: | Problem Note |
Priority: | high |
Date Modified: | 2013-07-10 13:49:40 |
Date Created: | 2013-07-09 14:09:19 |