6 Replies Latest reply: Sep 21, 2016 12:29 PM by Brad Richmond RSS

    Field Name Connectivity - Qlik Sense

      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

          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

              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!