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.

[Newsense2020]

Maybe I do not see a stupid mistake by myself, but the following script does not work. Could you have a look at it or write down how it should look like?

[Blacklist]:
LOAD
[E-Mail] AS [Blacklist_Email]
FROM [lib://DataFiles/Test_1.xlsx]
(ooxml, embedded labels, table is Blacklist);

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

When I delete the " as [Muster]" and refer to [E-Mail] in the if clause instwad of [Muster] it works?

[whiteymcaces]

Sorry, I didn't realise that you had changed the comparative value in the Exists statement.

The reason that "exists([Blacklist_Email],[Muster])" does not work is because you are comparing [Blacklist_Email] to a field that doesn't exist at the time of the Load Statement. The field name [Muster] does not exist until after the load has completed. You need to compare [Blacklist_Email] to the original field name being loaded in, [E-Mail]

if(exists([Blacklist_Email],[Muster]),'Ja','Nein') as Blacklist //will not work

if(exists([Blacklist_Email],[E-Mail]),'Ja','Nein') as Blacklist //works, I have already tested it.

[Newsense2020]

Ok, that is good to know that I was not completely wrong. But the issue is that in my original dataset the E-Mail is derived a s following:

Replace(Lower(Subfield(E_Mail, ';', 1)), 'e-mail', '') as Telefon_E_Mail ( the E-Mail is originaly derived as part of a string)

Therefore I cannot use the original statement. Is there any possibility to base the if clause on that?

[whiteymcaces · Accepted solution]

Try

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

[Newsense2020]

@whiteymcaces  That is working. What a logical solution in the end. Thank you!