Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
acbishop2
Creator
Creator

Merging rows with different IDs

Hello All,

I'm connecting two databases: 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.

LeadIdAppIdAppNameAppDate

11

100Donald Duck1/1/2018
22100Donald Duck1/1/2018
33101Mickey Mouse1/2/2018
44102Minnie Mouse1/3/2018
55102Minnie Mouse1/3/2018

I don't want to see the same app more than once. What approach do I need to take?

Labels (1)
1 Solution

Accepted Solutions
acbishop2
Creator
Creator
Author

I was able to do this using script similar to the following:

T1:

Load

     LeadId,

     If(Previous(AppId)=AppId,0,AppId) AS AppId,

     AppName,

     AppDate

From ...

Order by AppId;

Final:

Load

     LeadId,

     AppId,

     AppName,

     AppDate

Resident T1;

Drop Table T1;

This gets rid of the duplicates, but I now need a way to be able to choose which duplicate to remove and which to keep. I'll close this thread and open a new one here: Choose which duplicate to remove.

View solution in original post

1 Reply
acbishop2
Creator
Creator
Author

I was able to do this using script similar to the following:

T1:

Load

     LeadId,

     If(Previous(AppId)=AppId,0,AppId) AS AppId,

     AppName,

     AppDate

From ...

Order by AppId;

Final:

Load

     LeadId,

     AppId,

     AppName,

     AppDate

Resident T1;

Drop Table T1;

This gets rid of the duplicates, but I now need a way to be able to choose which duplicate to remove and which to keep. I'll close this thread and open a new one here: Choose which duplicate to remove.