topic Re: Field Name Connectivity - Qlik Sense in Connectivity & Data Prep

Field Name Connectivity - Qlik Sense

Tue, 20 Sep 2016 20:07:02 GMT

Hello all,

In my work, I have many "promotions" that consist of a number of discounted products. I also send out a weekly email that features many of these promotions at a time.

I have created a "PromoCode" that is unique to each promotion, as well as created a PromoCode reference table that contains relevant information. For example:

Fields: PromoCode PromotionName Discount PromoStartDate PromoEndDate

Data: abc123 Promotion1 30% 1/1/2016 2/1/2016

xyz456 Promotion2 50% 1/15/2016 2/15/2016

I also have an Email Metrics table that records the various send/open/click statistics for each email send. I would like to connect the PromoCode reference table to the Email Metrics table using the PromoCode. However, I need a way to connect multiple PromoCodes to each email iteration, as multiple promotions are included within each email. My table below fails because the "PromoCode" field from the reference table does not match up with the "PromoCode1", "PromoCode2", "PromoCode3", etc. fields within the Email Metrics table:

Fields: Email Name Subject Sent Opened PromoCode1 PromoCode2 PromoCode3

Data: Email1 Subject1 100 100 abc123 xyz456

Email2 Subject2 200 50 xyz456 def000 hij111

How can I resolve the naming issue but still connect individual PromoCodes to an email send (Email Name)?

Thanks in advance for any recommendations you may have!

Re: Field Name Connectivity - Qlik Sense

Gysbert_Wassenaar — Wed, 21 Sep 2016 10:55:54 GMT

Use a CrossTable load to change the structure of the email table so it matches up with the promotions

CrossTable(Dummy, PromoCode, 4)

LOAD [Email Name], Subject, Sent, Opened, PromoCode1, PromoCode2, PromoCode3

FROM ...;

Re: Field Name Connectivity - Qlik Sense

Wed, 21 Sep 2016 15:51:27 GMT

Hi gysbert,

Thank you for your response. I ended up changing the data source to mirror a CrossTable output:

Fields: Email Name Subject Sent Opened PromoCode

Data: Email1 Subject1 100 100 abc123

Email1 Subject1 100 100 xyz456

Email2 Subject2 200 50 xyz456

Email2 Subject2 200 50 def000

Email2 Subject2 200 50 hij111

However, I'm wanting to use "Sent" and "Open" fields as measures. Using the format above for the "Count" function is giving me 800 "Sent" and 350 "Opened", when the numbers should be 300 "Sent" and 150 "Opened". I can use the "distinct" to only count unique numbers in these fields, but I'm certain future emails will have the same "Sent" and "Opened" numbers. Is there an expression that will count "Sent" and "Opened" by another field distinctly, such as "Email Name"?

Thank you for your time!

Re: Field Name Connectivity - Qlik Sense

Wed, 21 Sep 2016 16:11:05 GMT

My apologies. In my previous response, I meant to say "Using the format above for the 'Sum' function is giving me...", not "Count".

Re: Field Name Connectivity - Qlik Sense

Gysbert_Wassenaar — Wed, 21 Sep 2016 16:23:46 GMT

Count(distinct ...) will give you the count of unique values for the given combination of dimension values.

If the combination of Email Name and Subject is not unique then you need to find a way to make a unique key for which there is only one distinct Sent and one distint Opened value.

Re: Field Name Connectivity - Qlik Sense

Wed, 21 Sep 2016 16:29:29 GMT

Could I not use the Email Name as the unique key? I only want to count Sends/Opens once for each email.

My thinking is along these lines: For each distinct Email Name, record Sends and Opens once. If the line item below has the same Email Name, do not count Sends and Opens. Continue this until reaching the next unique Email Name, then record Sends and Opens. And so on...

Can I use a combination of Sum and Distinct to achieve this?

Thank you again for your assistance!