Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
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.

14 Replies
Highlighted
Contributor III
Contributor III

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?

Highlighted
Partner
Partner

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.

Highlighted
Contributor III
Contributor III

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?

 

Partner
Partner

Try

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

View solution in original post

Highlighted
Contributor III
Contributor III

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