Pulling records with null field value

[lauriesamh]

Hi, I'm using CR 8.0. I have a sales table which has a primary account manager field. In some cases this field is blank and in this situation I need to pull the customer's primary account manager. To make it interesting I'm trying to group the managers into divisions based on a prior system that we converted from. How do I modify the @divisions formula to substitute the customer.PA_mgr instead of the division.PA_mgr when the sales.PA_mgr is null? See below

The tables I'm using are:
1. sales - has sales.PA_mgr
2. Customer - has customer.PA_mgr
3. division - division.PA_Mgr which relates to division.group

@divisions
if isnull({sales.PA_mgr}) then {customer.PA_mgr}
else
if {division.PA_Mgr} = "TCC" Then "TC" else
if {division.PA_Mgr} = "QC" Then "QC" else
if {division.PA_Mgr} = "BC" Then "BC" else
if {division.PA_Mgr} = "SC" and({sales.PA_mgr} in "##" to "#" then "FC"

 

[lupins46]

@divisions
stringvar mgr;
if isnull({sales.PA_mgr}) then
mgr:={customer.PA_mgr}
else
mgr:={division.PA_Mgr};

if mgr = "TCC" Then "TC" else
if mgr = "QC" Then "QC" else
if mgr = "BC" Then "BC" else
if mgr = "SC" and({sales.PA_mgr} in "##" to "#" then "FC"

 

[synapsevampire]

You can simplify a bit:
@divisions
stringvar mgr;
if isnull({sales.PA_mgr}) then
mgr:={customer.PA_mgr}
else
mgr:={division.PA_Mgr};
if mgr <> "SC" Then left(mgr,2)
else
if mgr = "SC"
and
{sales.PA_mgr} in ["#","##"] then
"FC"

Not sure what the {sales.PA_mgr} in "##" to "#" is intended to do, but it doesn';t seem that you're allowing for an SC when this isn't the case, hopefully never occurs.

If so, change it to:

if mgr <> "SC" Then left(mgr,2)
else
(
if mgr = "SC" then
if {sales.PA_mgr} in ["#","##"] then
"FC"
else
"SC"
)

This assumes that you'd want SC if {sales.PA_mgr} is not in ["#","##"].

-k

 

[lauriesamh]

The "##" is to designate the sales.PA_mgr number. I didn't include the number for privacy. There is definately PA_mgr numbers with that will go into FC grouping. Also there are also other groupings along with FC but I wanted to be brief.

I used the formula from the first tip:
@divisions
stringvar mgr;
if isnull({sales.PA_mgr}) then
mgr:={customer.PA_mgr}
else
mgr:={division.PA_Mgr};

if mgr = "TCC" Then "TC" else
if mgr = "QC" Then "QC" else
if mgr = "BC" Then "BC" else
if mgr = "SC" and({sales.PA_mgr} in "##" to "#" then "FC"
and now I'm getting mass duplications of sales records.

My concern is that the
if mgr = "SC" and({sales.PA_mgr} in "##" to "#" then "FC" isn't working properly.

Ideas?

 

[synapsevampire]

Did you look at my post regarding my concern for this?

It doesn't allow for an output if the PA_Mgr is not in the secret range.

-k

 

[lauriesamh]

Actually I did see. So sorry! I'm wondering if my duplication is occurring in one of two places.

The first place that might cause the duplication is I group the report by @Division, but my hunch is that the duplication is occurring in the record selection criteria on the division table.

I have criteria to pull the sales records when
{division.mgr_EFF_DT} <= {sales.SVC_DT} and
{division.mgr_TRM_DT} >= {sales.CLCL_LOW_SVC_DT}

How do I add in the record selection criteria when the sales.PA_mbr is null to use
(customer.mgr_EFF_Date) and customer.manager_Trm_Date)?

 

[synapsevampire]

First time you've mentioned duplication, you may want to read your posts thoroughly and describe the problem in full, you're assuming a great deal here.

I'll take a stab at it here:

if isnull(sales.PA_mbr) then
(
{customer.mgr_EFF_DT} <= {sales.SVC_DT}
and
{customer.mgr_TRM_DT} >= {sales.CLCL_LOW_SVC_DT} \
)
else
(
{division.mgr_EFF_DT} <= {sales.SVC_DT}
and
{division.mgr_TRM_DT} >= {sales.CLCL_LOW_SVC_DT}
)

-k

 

[lauriesamh]

K - You are correct in my assumptions and have chastised me and I am humbled. I'm tackling a rather complex issue (to me) due to a system conversion. I was hoping to be as brief as feasible.

I added the date criteria and it appears to work.

I still have some basic problems in that the sales records with null mgr field still aren't coming through.

I'm going to try a few things to see if I can resolve the problem.

Thanks

 

[synapsevampire]

Perhaps your joins aren't allowing for some rows?

Or perhaps they have a blank or "" instead of a null, which means you might try:

if isnull({sales.PA_mbr})
or
trim({sales.PA_mbr}) = ""
then
(
{customer.mgr_EFF_DT} <= {sales.SVC_DT}
and
{customer.mgr_TRM_DT} >= {sales.CLCL_LOW_SVC_DT} \
)
else
(
{division.mgr_EFF_DT} <= {sales.SVC_DT}
and
{division.mgr_TRM_DT} >= {sales.CLCL_LOW_SVC_DT}

-k

 

[lauriesamh]

I have done and outer left join on the division table to the sales record table.

The formula didn't seem to help.

I'm going to keep trying.

Thanks!

 

[synapsevampire]

Post everything you have in the record selection formulas (record and group).

-k