0

Exclude rows based on date field

David Wiser

April 27, 2018 22:41

None

Follow

We have a certificate table ties to the customer table containing the following fields:

Customer No. (key)

State (key)

Certificate No.

Effective Date (key)

Expiration Date

I'm trying to create rows which include any row where the expiration date is in a specified range and excludes any of those rows where a new certificate with an effective date on or after the beginning of the specific date range exists.  In effect, we are looking for those customers where we need a new certificate since the existing one is almost expired and we have not yet received a new one.  I can't seem to figure out how to exclude the certificate lines where we already have the new certificate.  Any ideas?

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Harry Lewis April 30, 2018 14:19

  Hello David.

  Perhaps something like this?

   

  D5: 1/1/2018
  D6: 3/31/2018

  =NL("Rows","Certificate",,"Expiration Date",NP("DateFilter",$D$5,$D$6),"Effective Date",NP("DateFilter","",$D$5-1))

• 0
  
  David Wiser April 30, 2018 15:03

  Probably was not as clear as I should have been.  Below is a screenshot example of the table.  As you can see, for customer AEN20 we have already received a new certificate starting 1/1/18 (row 3).  So, if I run the formula(s) for expiration date of 12/31/17, the result should only return row 1 since there is no new certificate for that customer.  Row 2 should be excluded based on the new certificate for that customer.  I am getting close but it is taking a while.

  

• 0
  
  Harry Lewis April 30, 2018 15:20

  Ah!

  In that case, how about something like this?

  First, we'll find out which customers already have a new certificate.

  D6: 12/31/2017

  D7: =NL("Filter","Certificate","Customer No.","Effective Date",NP("DateFilter","D6",""))

   

  Then we'll list all records where the Customer No. is *not* in that group:

  =NL("Rows","Certificate",,"Customer No.","<>"&NP("Join",D7,"&<>"))

   

  Edited by Harry Lewis April 30, 2018 15:20