Plan, execute, measure and optimize customer experiences

CALCULATED ITEM

Reply
Occasional Contributor
Posts: 5

CALCULATED ITEM

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,

Contributor
Posts: 36

Re: CALCULATED ITEM

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
Ask a Question
Discussion stats
  • 1 reply
  • 117 views
  • 0 likes
  • 2 in conversation