I'm connecting two tables: Leads and Apps. When a lead becomes an app, we record the AppId on the Leads table to connect it with the AppId on the App table and do so with a simple JOIN statement. Pretty simple so far, right? My issue stems from the fact that we decided to allow multiple leads to be connected to the same app. This is necessary for our business model, but has caused a problem for me when reporting. Because there may be two or more LeadIds for each AppId, some data is getting duplicated, as in this dummy data.
The problem with the data is that if I were to count(AppId), I would get 5 instead of the desired 3 (yes, I know I could do count(distinct(AppId)), but that wouldn't solve the actual problem). I was able to remove duplicates, but my script simply gets rid of the second one (or any after the first). Here's how it looks currently:
If(Previous(AppId)=AppId,0,AppId) as AppId,
Order by AppId;
Drop Table T1;
Instead of getting rid of all duplicates after the first, I need to be able to keep the one with a value in the CampaignId column. In the table above, I would keep the rows with LeadIds 11, 33, and 55.
I hope I articulated my need well enough. If anything didn't make sense, let me know. And as always, thanks in advance for the help!