Where not exists on concatenated field

[sibrulotte]

Hi, 

I have a table with email marketing results. If the same person opened and email and clicked through, I have two lines.

I want the line where the person opened not to show up in my table since I know they opened it if they clicked through.

Ex:

Email                                  Action     Date

kermit@gmail.com      open        2020-01-01

kermit@gmail.com      Click       2020-01-01

Piggy@gmail.com        Open       2020-01-01

I want my result table to be

Email                                  Action     Date

kermit@gmail.com      Click       2020-01-01

Piggy@gmail.com        Open       2020-01-01

Here's what I want to do:

Emailling:

Load

[email] & [Date] as Key_email_date

Email,

Action,

Date

from some_excel_file

where action = 'Click';

Load

[email] & [Date] as Key_email_date

Email,

Action,

Date

from some_excel_file

where not Exists ( Key_email_date;[email] & [Date]);

Unfortunatly, that second load returns nothing.

What could I be doing wrong?

[sibrulotte]

Side note: If I try it on just the email, things work out well...

[Brett_Bleess]

Have a look at the following Community post, believe what you may be missing is Group/Order by to get things in the proper order to consistently pick up what you want, if you search further, I am pretty sure you can likely find other posts that may be helpful too.

https://community.qlik.com/t5/QlikView-Scripting/load-only-the-most-recent/m-p/623737#M38948

Regards,
Brett

[sibrulotte · Accepted solution]

thx for the reply.

What I actually did though was quite simple.

//Charger uniquement les Clickthrough
Courriels:
LOAD
Left(FileName() , 10 ) &email as CLE_Date_email,
Left(FileName() , 10 ) as Date_envois_courriel,
email ,
action,
total,
extra
FROM
[C:\*.xlsx]
(ooxml, embedded labels, table is DATA)

where action = 'clickthru';

//Charger l'ensemble des courriels
Courriel_Bis:
LOAD
Left(FileName() , 10 ) &email as CLE_Date_email_bis,
Left(FileName() , 10 ) as Date_envois_courriel_bis,
email as email_bis ,
action as action_bis,
total as total_bis,
extra as extra_bis
FROM
[C:\*.xlsx]
(ooxml, embedded labels, table is DATA)
;

//à partir de l'ensemble des courriels, ne retenir que ceux qui n'ont pas de Click through afin d'éviter les doublons "Opened" pour ceux qui ont un Clickhrough.

LOAD
CLE_Date_email_bis as CLE_Date_email,
Date_envois_courriel_bis as Date_envois_courriel,
email_bis as email ,
action_bis as action,
total_bis as total,
extra_bis as extra
resident Courriel_Bis
where not Exists (CLE_Date_email, CLE_Date_email_bis);

drop tables Courriel_Bis;