Forum Discussion
Extracting data based on date value
For eg customer-id, order-id, reciept-no and date of purchase
Where customers-id, order-id and date of purchase might have duplicate values however reciept-no is unique
I want to extract data based on date of purchase where the date of purchase should be the oldest one for every customer-id, if there are 3 records for any customer-id it should extract the rows having the two oldest dates
And if any customer-id has 2 records it should fetch the 1 row for the oldest date.
6 Replies
Hi
Could you attach a mocked up file to demonstrate your source data and how you want the results to appear.
Can I ask why you want the oldest 2 records when there are 3 or more but only the 1 oldest when there are 2 records?
a little clarification
i need to extract all the oldest records and ignore the most recent one
no matter whatever the case is
================Can I ask why you want the oldest 2 records when there are 3 or more but only the 1 oldest when there are 2 records?
================
if there are 4 records i need the oldest 3
if there are 3 records i need the oldest 2
and so on
just to add more clarification
we i need the data for the cases where the Customer No & Code match are repeating themselves in the table, and all the records where the Creation date is older then then recent (created on )date
for example
for customer no 3009832402
Reciept no
customer no
code
created on
320687056 3009832402 34 13/04/2018 320750458 3009832402 44 22/04/2018 320687149 3009832402 44 13/04/2018 the result should only extract the row in bold and italic
and ignore all code which are not similar for the customer no 3009832402
and ignore the row with the latest creation date for that code
I've attached a mock up of potential formula you could use (3 new columns) to then provide you with a column you can filter by called "Is oldest duplicate".
