Forum Discussion
Solved
Calculated column based on ID match between two table and time comparison
Calculated column needs to created in transaction table1 if its Unique ID match with the call data table 2 unique ID and call was made within 10mins before the transaction time Table1 : Transaction ...
Hi,
Write these calculated column formulas
Pause entries within half an hour prior = CALCULATE(COUNTROWS(Agent),FILTER(Agent,Agent[Agent]=EARLIER('Transaction'[Agent])&&Agent[Transaction Date]=EARLIER('Transaction'[Transaction Date])&&Agent[Time]<=EARLIER('Transaction'[Time])&&Agent[Time]>=EARLIER('Transaction'[Time])-time(0,30,0)&&Agent[Value]="Pause"))Farthest time stamp in the agent table prior to the time stamp in this table = CALCULATE(max(Agent[Time]),FILTER(Agent,Agent[Agent]=EARLIER('Transaction'[Agent])&&Agent[Transaction Date]=EARLIER('Transaction'[Transaction Date])&&Agent[Time]<=EARLIER('Transaction'[Time])))Reuslt = if('Transaction'[Farthest time stamp in the agent table prior to the time stamp in this table]=BLANK(),"Missing",if('Transaction'[Pause entries within half an hour prior]=BLANK(),"No pause","Pause"))
Hi Ashish,
Thank you so much for the help.
Logic: If call made (Call table) is within 30 mins before the matching transaction time (Transaction Table) and then return OUTPUT column if call table has any Pause (within last 30 mins) , if not then if it has only No Pause then output No Pause, else no matching calls in last 30 mins then Missing
Please find below
Data :
| Table1 : Transaction Table | ||||
| Agent Transaction Date | Time | Unique ID | ||
| A46-14/01/2022 | 10:40:00 | A46-14/01/2022-10:40:00 | ||
| A16-15/01/2022 | 16:00:00 | A16-15/01/2022-16:00:00 | ||
| A12-16/01/2022 | 14:00:00 | A16-16/01/2022-14:00:00 | ||
| Table2 : Call Table | ||||
| Agent Call Date | Time | Unique ID | Value | |
| A46-14/01/2022 | 10:30:00 | A46-14/01/2022-10:20:00 | Pause | |
| A46-14/01/2022 | 10:45:00 | A46-14/01/2022-10:20:00 | No Pause | |
| A46-14/01/2022 | 10:28:00 | A46-14/01/2022-10:20:00 | No Pause | |
| A16-15/01/2022 | 15:58:00 | A16-15/01/2022-16:00:00 | No Pause | |
| A16-15/01/2022 | 15:28:00 | A16-15/01/2022-16:00:00 | No Pause | |
| A16-15/01/2022 | 15:38:00 | A16-15/01/2022-16:00:00 | No Pause | |
| A12-16/01/2022 | 14:01:00 | A16-16/01/2022-14:00:00 | Pause | |
| A12-16/01/2022 | 14:02:00 | A16-16/01/2022-14:00:00 | No Pause | |
| New Column in Transaction Table | ||||
| Agent Transaction Date | Time | Unique ID | output | Reason |
| A46-14/01/2022 | 10:40:00 | A46-14/01/2022-10:40:00 | Pause | As Transaction table1 has entry at 10:40:00 and 30 mins before that call table has one entry of Pause hence output Pause |
| A16-15/01/2022 | 16:00:00 | A16-15/01/2022-16:00:00 | No Pause | As Transaction table1 has entry at 16:00:00 and 30 mins before that call table has only entry of entry of No Pause hence o/p No Pause |
| A12-16/01/2022 | 14:00:00 | A16-16/01/2022-14:00:00 | Missing | As Transaction table1 has entry at 14:00:00 i.e. no call before that time in table 2 hence output is Missing |
Hi,
Write these calculated column formulas
Pause entries within half an hour prior = CALCULATE(COUNTROWS(Agent),FILTER(Agent,Agent[Agent]=EARLIER('Transaction'[Agent])&&Agent[Transaction Date]=EARLIER('Transaction'[Transaction Date])&&Agent[Time]<=EARLIER('Transaction'[Time])&&Agent[Time]>=EARLIER('Transaction'[Time])-time(0,30,0)&&Agent[Value]="Pause"))Farthest time stamp in the agent table prior to the time stamp in this table = CALCULATE(max(Agent[Time]),FILTER(Agent,Agent[Agent]=EARLIER('Transaction'[Agent])&&Agent[Transaction Date]=EARLIER('Transaction'[Transaction Date])&&Agent[Time]<=EARLIER('Transaction'[Time])))Reuslt = if('Transaction'[Farthest time stamp in the agent table prior to the time stamp in this table]=BLANK(),"Missing",if('Transaction'[Pause entries within half an hour prior]=BLANK(),"No pause","Pause"))
Thannk you so much for you help and providing solution for this