How to relate 2 fields without showing the common field?

[Jesus]

Hi guys, I need your help, I have a table 1, there a field that counts cancellations and retentions in a day, I want to know how many cases of cancellations have retention that day, since not all retention cases have a cancellation case.

I have the field contactId, this field relates the IDs of cases that have cancellation and retention (table 2) that day, but I don’t want this id field to be shown.

In the image, I show you table 1 and table 2, I want to create table 3. In table 2 I show you in yellow color, that there are 2 clients (contactID) that have a cancellation case with retention, then I want to reflect in table 3 that amount without showing the contactID field.

[Koushik_Muthanna]

Hello,

Assumption:

1. you have a dataset as shown in table 2 but in a tabular format.

Steps :

1. for table 2 : create a new column using a calculated field ( count of contact id which is partitioned by create date and contact id . If the value of count is > 1 , then 1 , else 0 ).
2. for table 3 : create another calculated field ( if cancellation value is 1 and value of calculated field in step1 is 1 , then 1 , else 0 ) .

Regards,
Koushik