Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All, Need ideas for removing duplicate of ticket numbers from a set of data.
Every time there is a change request the draft number creates assigned to the same ticket.
However, I need only the latest tickets based on the latest draft number of the ticket for the same market.
Below is a sample of data, any ideas are helpful. highlighted in different colors to show they
Ticket Number | Draft | Market | Job Type | Status | Date |
CH-2019-0430 | 4 | Spain | Static PDF/PPT | Approved | 02-01-2020 |
CH-2019-0430 | 5 | Spain | Static PDF/PPT | Approved | 07-02-2020 |
CH-2019-0661 | 4 | France | Localisation | Approved | 02-01-2020 |
CH-2019-0903 | 2 | Hong Kong | Localisation | Approved | 02-01-2020 |
CH-2019-0915 | 1 | Slovakia | Localisation | Approved | 02-01-2020 |
CH-2019-0916 | 1 | Slovakia | Localisation | Approved | 02-01-2020 |
CH-2019-0924 | 1 | Belgium | Interactive PPT/PDF | Approved | 02-01-2020 |
CH-2019-0925 | 1 | Belgium | Interactive PPT/PDF | Approved | 02-01-2020 |
CH-2019-0929 | 1 | France | Static PDF/PPT | Approved | 02-01-2020 |
CH-2020-0941 | 1 | India | HTML Conversion (PDF) | Approved | 13-01-2020 |
CH-2020-0942 | 1 | India | HTML Conversion (PDF) | Approved | 13-01-2020 |
CH-2020-0943 | 1 | India | HTML Conversion (PDF) | Approved | 13-01-2020 |
CH-2020-0944 | 1 | India | HTML Conversion (PDF) | Approved | 13-01-2020 |
CH-2020-0945 | 1 | India | HTML Conversion (PDF) | Approved | 13-01-2020 |
CH-2019-0921 | 2 | Germany | Interactive PPT/PDF | Approved | 07-01-2020 |
CH-2019-0937 | 1 | Poland | Interactive PPT/PDF | Approved | 07-01-2020 |
CH-2020-0946 | 1 | France | Interactive PPT/PDF | Approved | 09-01-2020 |
CH-2020-0946 | 2 | France | Interactive PPT/PDF | Approved | 16-01-2020 |
CH-2020-0946 | 3 | France | Interactive PPT/PDF | Approved | 24-01-2020 |
CH-2020-0946 | 4 | France | Interactive PPT/PDF | Approved | 03-02-2020 |
CH-2020-0946 | 5 | France | Interactive PPT/PDF | Approved | 18-02-2020 |
CH-2020-0946 | 6 | France | Interactive PPT/PDF | Approved | 12-03-2020 |
CH-2019-0857 | 2 | USA | Localisation | Approved | 03-02-2020 |
CH-2019-0857 | 3 | USA | Localisation | Approved | 18-02-2020 |
CH-2019-0863 | 2 | Great Britain | Localisation | Approved | 06-01-2020 |
CH-2020-0947 | 1 | Finland | Interactive PPT/PDF | Approved | 08-01-2020 |
CH-2019-0898 | 2 | Portugal | Localisation | Staged | |
CH-2019-0898 | 3 | Portugal | Localisation | Staged | |
CH-2019-0898 | 4 | Portugal | Localisation | Staged | |
CH-2019-0898 | 5 | Portugal | Localisation | Approved | 24-01-2020 |
CH-2020-0948 | 1 | Canada | Static PDF/PPT | Approved | 29-01-2020 |
CH-2020-0949 | 1 | Moldova | Localisation | Staged | |
CH-2020-0949 | 2 | Moldova | Localisation | Approved | 26-03-2020 |
CH-2020-0950 | 1 | Moldova | Localisation | Approved | 10-01-2020 |
CH-2019-0894 | 2 | Latvia | Localisation | Staged | |
CH-2019-0894 | 3 | Latvia | Localisation | Staged | |
CH-2019-0894 | 4 | Latvia | Localisation | Approved | 20-01-2020 |
CH-2020-0951 | 1 | Lithuania | Localisation | Staged | |
CH-2020-0951 | 2 | Lithuania | Localisation | Staged | |
CH-2020-0951 | 3 | Lithuania | Localisation | Approved | 07-02-2020 |
CH-2020-0952 | 2 | Ireland | Static PDF/PPT | Approved | 07-01-2020 |
CH-2020-0953 | 1 | Estonia | Localisation | Staged | |
CH-2020-0953 | 2 | Estonia | Localisation | Staged | |
CH-2020-0953 | 3 | Estonia | Localisation | Approved | 23-01-2020 |
CH-2019-0699 | 5 | Spain | Localisation | Approved | 14-01-2020 |
CH-2019-0699 | 6 | Spain | Localisation | Staged | |
CH-2019-0699 | 7 | Spain | Localisation | Staged | |
CH-2019-0699 | 8 | Spain | Localisation | Staged | |
CH-2019-0699 | 9 | Spain | Localisation | Approved | 28-04-2020 |
CH-2019-0931 | 1 | Canada | Static PDF/PPT | Approved | 08-01-2020 |
CH-2019-0932 | 1 | Canada | Static PDF/PPT | Approved | 08-01-2020 |
CH-2019-0933 | 1 | Canada | Static PDF/PPT | Approved | 08-01-2020 |
CH-2019-0934 | 1 | Canada | Static PDF/PPT | Approved | 08-01-2020 |
CH-2019-0902 | 2 | Canada | Interactive PPT/PDF | Staged | |
CH-2019-0902 | 3 | Canada | Interactive PPT/PDF | Staged | |
CH-2019-0902 | 4 | Canada | Interactive PPT/PDF | Approved | 25-02-2020 |
CH-2020-0954 | 1 | Canada | Static PDF/PPT | Deleted | |
CH-2020-0955 | 1 | Canada | Static PDF/PPT | Staged | |
CH-2020-0955 | 2 | Canada | Static PDF/PPT | Approved | 29-01-2020 |
CH-2020-0956 | 1 | Ireland | Interactive PPT/PDF | Approved | 10-01-2020 |
CH-2020-0957 | 1 | Ireland | Video | Approved | 09-01-2020 |
CH-2020-0958 | 1 | Ireland | |||
CH-2020-0959 | 1 | Ireland | Video | Approved | 09-01-2020 |
CH-2019-0741 | 3 | Latvia | Localisation | Approved | 10-01-2020 |
CH-2020-0960 | 1 | France | Video | Approved | 14-01-2020 |
CH-2020-0961 | 1 | France | Video | Approved | 14-01-2020 |
CH-2020-0962 | 1 | Great Britain | Interactive PPT/PDF | Staged | |
CH-2020-0962 | 2 | Great Britain | Interactive PPT/PDF | Approved | 23-01-2020 |
CH-2019-0806 | 1 | Denmark | Static PDF/PPT | Approved | 10-01-2020 |
CH-2019-0806 | 2 | Denmark | Static PDF/PPT | Approved | 10-02-2020 |
CH-2019-0022 | 6 | Romania | Localisation | Approved | 04-02-2020 |
CH-2020-0963 | 1 | Denmark | Static PDF/PPT | Approved | 10-01-2020 |
CH-2020-0963 | 2 | Denmark | Static PDF/PPT | Approved | 10-02-2020 |
CH-2020-0964 | 1 | Benin | |||
CH-2020-0965 | 1 | France | Static PDF/PPT | Approved | 03-02-2020 |
CH-2020-0966 | 1 | France | Video | Approved | 14-01-2020 |
CH-2020-0967 | 1 | France | Video | Approved | 14-01-2020 |
CH-2020-0968 | 1 | Nigeria | Video | Approved | 14-01-2020 |
CH-2019-0924 | 2 | Belgium | Interactive PPT/PDF | Approved | 10-01-2020 |
CH-2019-0925 | 2 | Belgium | Interactive PPT/PDF | Approved | 10-01-2020 |
CH-2020-0969 | 1 | Great Britain | Localisation | Staged | |
CH-2020-0969 | 2 | Great Britain | Localisation | Approved | 30-01-2020 |
CH-2020-0969 | 3 | Ireland | Localisation | Approved | 05-02-2020 |
CH-2019-0903 | 3 | Hong Kong | Localisation | Staged | |
CH-2020-0970 | 1 | France | Video | Approved | 10-01-2020 |
CH-2020-0971 | 1 | France | Video | Approved | 10-01-2020 |
CH-2020-0972 | 1 | Philippines | Localisation | Staged | |
CH-2020-0972 | 2 | Philippines | Localisation | Staged | |
CH-2020-0972 | 3 | Philippines | Localisation | Staged | |
CH-2020-0973 | 1 | France | Video | Approved | 10-01-2020 |
CH-2020-0974 | 1 | France | Video | Approved | 10-01-2020 |
CH-2020-0975 | 1 | France | Video | Approved | 10-01-2020 |
CH-2020-0976 | 1 | France | Video | Approved | 10-01-2020 |
CH-2020-0977 | 1 | India | HTML Conversion (PDF) | Approved | 13-01-2020 |
CH-2020-0978 | 1 | India | HTML Conversion (PDF) | Approved | 13-01-2020 |
CH-2020-0979 | 1 | Benin | Static PDF/PPT | Expired | 20-01-2020 |
CH-2020-0980 | 1 | Cameroon | Static PDF/PPT | Expired | 20-01-2020 |
CH-2020-0981 | 1 | Congo | Static PDF/PPT | Expired | 20-01-2020 |
CH-2020-0982 | 1 | Gabon | Static PDF/PPT | Expired | 20-01-2020 |
Solved! Go to Solution.
@Anonymous ,
Glad to hear that! I have moved the case from issues forum to desktop forum. Could you please mark the appropriate reply as a solution? Thank you very much.
Regards,
Jimmy Tao
idea provided by v-yuta-msft worked with littlet enhancements made to meet the requirements.
Thank youv-yuta-msft for the idea, definatly it is a solution. is there a way to mark it as solution.
@Anonymous ,
Glad to hear that! I have moved the case from issues forum to desktop forum. Could you please mark the appropriate reply as a solution? Thank you very much.
Regards,
Jimmy Tao
@Anonymous ,
In query editor, click duplicate the table, in the original table, click transform-> group by as below:
Then merge the original table with the duplicated table using left outer join as below:
Regards,
Jimmy Tao
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |