Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Lookup other Table based on Product and Date range

Hey!   Current data example, working in the insurance business:   Table 1: Insurances gained Customer_ID |  InsuranceNumber(unique) |  Product  |    Date        | Indicator_Moved_policy 1      ...
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , try like

     

    Assume you have a date
    New column in Table 1


    Indicator_Moved_policy= if(countx(filter(Table2, table1[Customer_ID] =table2[Customer_ID] && table1[Product] =table2[Product] && table1[Date] >=table2[Date] -90 && table1[Date] <=table2[Date] +90), Table2[Customer_ID])+0>0,1,0)

     

    New column in Table 2


    Indicator_Moved_policy= if(countx(filter(Table1, table1[Customer_ID] =table2[Customer_ID] && table1[Product] =table2[Product] && table2[Date] >=table1[Date] -90 && table2[Date] <=table1[Date] +90), Table1[Customer_ID])+0>0,1,0)

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , try like

 

Assume you have a date
New column in Table 1


Indicator_Moved_policy= if(countx(filter(Table2, table1[Customer_ID] =table2[Customer_ID] && table1[Product] =table2[Product] && table1[Date] >=table2[Date] -90 && table1[Date] <=table2[Date] +90), Table2[Customer_ID])+0>0,1,0)

 

New column in Table 2


Indicator_Moved_policy= if(countx(filter(Table1, table1[Customer_ID] =table2[Customer_ID] && table1[Product] =table2[Product] && table2[Date] >=table1[Date] -90 && table2[Date] <=table1[Date] +90), Table1[Customer_ID])+0>0,1,0)

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Cool! That seems to work. Is there a way to have a build in check, to make sure it isn't the same policy number?

Can you add like a && Table1[InsuranceNumber] <> Table2[InsuranceNumber] && in between?