Filter duplicates in dataset
I am trying to create a table which has the sum of all the hours booked for a specific task which is filtered for after a specific date but for any WI_ID's which remain after the filter has taken effect, all of the hours are added including those before the filter cut off date.
I have attempted several solutions to solve this. below is a description of my closest attempt but it is still not right
I currently have 2 almost identical datasets which are a joining of the LIVE_TIMESHEET_ENTRIES and LIVE_WORKITEM_CNT through the LINKS dataset. one of the datasets however has been filtered for START_DATE (filter based on user input) and it means that some of the WI_ITEMID entries have been removed leaving only the WI_ID's I want BUT there are duplicates. I have then created a joint dataset between these 2 datasets which has filtered out the WI which fall outside of the filter whilst adding up all the hours for WI's which are still present and required
The problem I'm having is that if the filtered table has multiple occurrences of a WI_ITEMID then I get a multiple of the total hours worked on that WI.
i.e. The second table I need to filter for duplicate occurrences of WI_ITEMID in order to make it work
I have tried using a filter but there is no UNIQUE function to filter it by
I have tried looking on line and there appears to be a solution if I were using SQL of using SELECT DISTINCT but no JAZZ equivalent.
I've tried looking for a count function inside the table to count the occurrences of each WI but I can't find it.
Any help would be greatly appreciated