Check if value already exists in another table

[Newsense2020]

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.	Name	E-Mail	Blacklist
1	Oliver	oliver@gmail.com	no
2	Dieter	Dieter@gmail.com	no
3	Hannes	Hannes@gmail.com	yes

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.

[dplr-rn]

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

[Newsense2020]

@dplr-rn  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?

[dplr-rn]

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 ). 

[Newsense2020]

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.

[whiteymcaces]

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.

[Newsense2020]

@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!

[whiteymcaces]

Can you attach your example qvw and xlsx files?

[Newsense2020]

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;

[whiteymcaces]

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')"