Solved: Filtering Duplicate Emails - Qlik Community

haymarketpaul · ‎2012-01-30

Hello

I have 2 separate lists of email addresses (online & bulletin)

Lets say for online i have:

and for bulletin i have:

i need to be able to select email addresses that are either:

1. only online email addresses

2. only bulletin email addresses

3. email addresses that appear in both online AND bulletin lists

Not sure of the best way to handle this in the LOAD script to then display and be able to select either of these scenarios?

swuehl · ‎2012-01-30

I would suggest that you concatenate both lists, and create a new field to filter the origin, maybe using something like:

INPUT:

LOAD *, 'Online' as Type INLINE [

email

];

LOAD *, 'Bulletin' as Type INLINE [

email

];

RESULT:

NOCONCATENATE LOAD email, if(minstring(Type)=maxstring(Type), only(Type), 'Both') as Type resident INPUT group by email;

drop table INPUT;

corrected script to label first load correctly

View solution in original post

swuehl · ‎2012-01-30

I would suggest that you concatenate both lists, and create a new field to filter the origin, maybe using something like:

INPUT:

LOAD *, 'Online' as Type INLINE [

email

];

LOAD *, 'Bulletin' as Type INLINE [

email

];

RESULT:

NOCONCATENATE LOAD email, if(minstring(Type)=maxstring(Type), only(Type), 'Both') as Type resident INPUT group by email;

drop table INPUT;

corrected script to label first load correctly

haymarketpaul · ‎2012-01-31

Works perfectly Thanks

was missing this crucial bit...very clever.

if(minstring(Type)=maxstring(Type), only(Type), 'Both') as Type