Forum Discussion
Custom function to extract data from table
Hi all,
I have a table with a date and an identifier field.
The identifier field can have unique values as well as multiple entries of a unique identifier (ID).
Simplified:
Date -> ID1
Date -> ID2*
Date -> ID2'
Date -> ID3*
Date -> ID3#
Date -> ID3'
What I am interested in is the time frame between the first (e.g. ID2* or ID3*) and last (e.g. ID2' or ID3') mention of an identifier, i.e. unique values (like ID1) and values in between the first and last identifier (like ID3#) need to be disregarded/deleted.
Is there any way I can do this?
I know that I can simply subtract the dates within excel but I am struggling with removing the unique lines and the lines in between the first and last mention of an identifier.
An example file is attached. The table I have has a lot more rows (27.000) and Identifiers can be mentioned up to 8 times in the table.
Thanks so much in advance and best wishes
RaMa87 I used three queries. First one is simply a direct copy of the table, sorted ascending on date (qryStartDates), Second is identical but sorted Descending on date (qryEndDates). Third is a full join of 1 and 2 on the ID column, filtered out the ones where dates from 1 and 2 are the same, then subtracted both dates.
4 Replies
RaMa87 I used three queries. First one is simply a direct copy of the table, sorted ascending on date (qryStartDates), Second is identical but sorted Descending on date (qryEndDates). Third is a full join of 1 and 2 on the ID column, filtered out the ones where dates from 1 and 2 are the same, then subtracted both dates.
Brilliant! Thank you so much!
I additionally removed duplicates and transformed the output to only contain positive values (absolute number function) --> exactly what I was looking for.
Along those lines I came across a similar but rather more difficult issue (also maybe difficult to explain)... Do you have by chance a similar brilliant solution?
I have a similar table with a date, an identifier and a number (example attached).
At the moment the original data is sorted by date (i.e. pretty much scrambled up).In theory the data needs to be sorted by date AND by ID - then I am interested in the 'Number' tab.
The number can (but does not necessarily) start with 0 and increases over time (date) for each ID until it drops again to 0.
I am looking for a solution which only returns the number before a 0 for an identifier.
(Hopefully the example explains it a bit better)
I searched for a solution quite some time and did not come up with a smart one:-(
RaMa87 Sure. I used this trick twice to get data from the next record: https://www.linkedin.com/pulse/get-transform-rescue-wyn-hopkins/
