Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
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?

 

Highlighted
Partner
Partner

Try

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

View solution in original post

Contributor III
Contributor III

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