Forum Discussion
Custom function to extract data from table
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.
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/
JKPieterse Thanks! that helps a lot 🙂