Forum Discussion
Flag based on value on multiple rows
Hi,
I have a data set that contains a unique value for each customer as well as a column which indicates what offer they received as well as the method of communication (channel). For example;
| ID | Offer | Received Letter | Received Email | Received Phone Call |
| 1 | 20% off | Y | N | N |
| 1 | 20% off | N | N | Y |
| 2 | 15% off | N | Y | N |
| 2 | 15% off | N | N | Y |
| 2 | 20% off | Y | N | N |
| 3 | 15% off | Y | Y | N |
| 3 | 20% off | N | Y | N |
| 3 | 50% off | Y | N | Y |
What I need to do is create a column/columns which provide me with the channel combinations. For example, in the above, customer number 1 has received a letter and a phone call for the same offer, so ideally i would liek to be able to have a flag that indicates that they have received this combination (letter & phone call). The most important thing though is that this would need to be based on the same offer, as some customers may receive a letter and a call for different offers.
Ideally I'd love it to look like this
| Based on ID & Offer | ||||||||||
| ID | Offer | Received Letter | Received Email | Received Phone Call | Letter Only | Email Only | Phone Call Only | Letter & Phone Call | Letter & Email | Email & Phone Call |
| 1 | 20% off | Y | N | N | N | N | N | Y | N | N |
| 1 | 20% off | N | N | Y | N | N | N | Y | N | N |
| 2 | 15% off | N | Y | N | N | N | N | N | N | Y |
| 2 | 15% off | N | N | Y | N | N | N | N | N | Y |
| 2 | 20% off | Y | N | N | Y | N | N | N | N | N |
| 3 | 15% off | Y | Y | N | N | N | N | N | Y | N |
| 3 | 20% off | N | Y | N | N | Y | N | N | N | N |
| 3 | 50% off | Y | N | Y | N | N | N | Y | N | N |
Like this? See last row, a duplicate of the first row with a Y added to Email
17 Replies
What if it looked like this for ID 1?
Based on ID & Offer ID Offer Received Letter Received Email Received Phone Call Letter Only Email Only Phone Call Only Letter & Phone Call Letter & Email Email & Phone Call 1 20% off Y N Y N N N Y N N You should be able to create measures like this:
Email Only = VAR tmpTable = SUMMARIZE(Table8,[ID],[Offer],"RL",MAX([Received Letter]),"RE",MAX([Received Email]),"RP",MAX([Received Phone Call])) VAR RL = MAXX(tmpTable,[RL]) VAR RE = MAXX(tmpTable,[RE]) VAR RP = MAXX(tmpTable,[RP]) RETURN IF(RL="Y" && RE="N" && RP="N","Y","N")