BookmarkSubscribeRSS Feed
keiwo
Obsidian | Level 7

Greetings,

 

I am trying to create a calculated item to use in my COMMUNICATION_NODE

 

calculated item => CONTACT_NO

 

Logic:

  1. contact_no = home_no
  2. if home_no is empty then contact_no = work_no
  3. if work_no is empty then contact_no = celluar_no

 

I'm just not quite sure how to setup the formula in the CALCULATED_ITEM expression section.

 

Thanks,

1 REPLY 1
Dmitry_Alergant
Pyrite | Level 9

Hi,

 

If Home, Work and Celluar number are all columns in the same table which is part of your datamart (like a customer table), then a calculated item is very straightforward.  

 

A more generic and flexible approach is a "case" statement. You can resolve many different challenges with this thing. In this case, it will be

 

 

 

case
when <</Home_No>> is not missing then <</Home_No>>
when <</Work_No>> is not missing then <</Work_No>>
else <</Cellular_No>>
end

 

Clearly you will have to replace the <</Home_No>> tags with your actual data item identifiers, as you insert them to the formula editor.

 

 

In this particular case, you can also try to use a coalescec function:

 

 

coalescec (<</Home_No>>, <</Work_No>>, <</Cellular_No>>)

 

 

If however your data are in a vertical table with one phone number per line, and a phone type indicator in another column (as it sometimes happens), choosing one phone number from such table would also be possible, but a little bit more trickier. This I think goes beyond the scope of your question.

 

 

 

-------
Dmitriy Alergant, Tier One Analytics
How to improve email deliverability

SAS' Peter Ansbacher shows you how to use the dashboard in SAS Customer Intelligence 360 for better results.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1552 views
  • 0 likes
  • 2 in conversation