Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Check if value already exists in another table

Hello experts,

I am starting Qlik Sense at a new company and need to convince my colleauges quite quickly. Therefore I need some urgent help on the following issue:

I have uploaded two tables. The first only contains E-Mails from a Blacklist (without any other information). The second table contains Customer information. 

 

Nr.NameE-MailBlacklist
1Oliveroliver@gmail.comno
2DieterDieter@gmail.comno
3HannesHannes@gmail.comyes

 

My goal is now to have such a list (first three information from table 2, fourth information derived from table 1). Sounds quite easy but the solutions I have found so far did not work out.

I would be grateful for any help.

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Check if value already exists in another table

Try

if(exists([Blacklist_Email], Replace(Lower(Subfield(E_Mail, ';', 1)), 'e-mail', '')), 'Ja', 'Nein') as Blacklist

View solution in original post

14 Replies
Highlighted
Partner
Partner

Re: Check if value already exists in another table

you can do it using a apply map .

e.g. rough code below

blacklistmap:
mapping load
email,
'Yes'
from blacklist //blacklist source
;

load 
*
,ApplyMap('blacklistmap',email,'No') as blacklist 
from
regulardata

 

Highlighted
Contributor III
Contributor III

Re: Check if value already exists in another table

@dilipranjith  Thanks for your quick response. To be honest I would prefer a solution within a diagram (table), not within the script as the already existing script is (for me) quite complicated. Maybe you have an idea for that kind of solution?

Highlighted
Partner
Partner

Re: Check if value already exists in another table

solution depends on your data model.

share a picture and more details of you data model  .

on a high level you will have to rename it and have 2 email columns with different names (if the blacklist table only has email id ). 

Highlighted
Contributor III
Contributor III

Re: Check if value already exists in another table

Here is an exemplary Data file comparable to the real dataset, which I unfortunately cannot share as it is based on customer data.

In the first tab you have the customer data and in the second tab the in fact just a list of e-mail-adresses.

Highlighted
Partner
Partner

Re: Check if value already exists in another table

Dilipranjith's solution is a great option. You will struggle to find a solution using Calculated Dimensions or Expressions in a chart. If it is possible, it will be 100 x more complicated then a simple script change.

Another option is to make sure that the Blacklist Table is loaded first in the script and change the name of the field for the email in the Blacklist table (or create a second field

i.e.

BlackList:

Load

[E-Mail] as tempemail

From Blacklist;

OR

BlackList:

Load

[E-Mail],

[E-Mail] as tempemail

From Blacklist;

Then add this in the load statement when adding the Customer information:-

If(Exists(tempemail, [E-Mail]), 'Yes', 'No') as Blacklist

There's a few ways you can do it, but they all require changing the script.

Highlighted
Contributor III
Contributor III

Re: Check if value already exists in another table

@whiteymcaces  Thanks. That worked out for the exemplary file.  Now I am facing a new challenge with the real data. The customers E-Mail is derived and ends with an as [-Mail]

Applied For the exemplary date: 

This loading statement works:

[Data]:
LOAD
[Nr.],
[Name],
[E-Mail],
if(exists([Blacklist Email],[E-Mail]), 'Ja','Nein')
FROM [lib://DataFiles/Test_1.xlsx]
(ooxml, embedded labels, table is Data;

 

This loading statement does not works:

[Data]:
LOAD
[Nr.],
[Name],
[E-Mail] as [Test],
if(exists([Blacklist Email],[Test]), 'Ja','Nein')
FROM [lib://DataFiles/Test_1.xlsx]

 

Do you have a solution for that?

 

Thanks in advance!

 

 

Highlighted
Partner
Partner

Re: Check if value already exists in another table

Can you attach your example qvw and xlsx files?

Highlighted
Contributor III
Contributor III

Re: Check if value already exists in another table

Attached you can find the exemplary data and qvf. If I change the second load statement to  the following it does not work:

[Data]:
LOAD
[Nr.],
[Name],
[E-Mail] as [Test],
if(exists([Blacklist Email],[Test]), 'Ja','Nein')
FROM [lib://DataFiles/Test_1.xlsx]
(ooxml, embedded labels, table is Data;

 

Highlighted
Partner
Partner

Re: Check if value already exists in another table

Firstly, you need to change your script to what I originally had and add a field name.

if(exists([Blacklist Email],[Test]), 'Ja','Nein') as Blacklist

 

Then in your chart, simply use the field "Blacklist" as a column, not "if(exists([Blacklist Email],[Test]), 'Ja','Nein')"