Field Name Connectivity - Qlik Sense

[#1]

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!

[Gysbert_Wassenaar]

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 ...;

[#3]

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!

[#4]

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

[Gysbert_Wassenaar]

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.

[#6]

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!