6 Replies Latest reply: Dec 4, 2015 12:40 PM by Paolo Ferri RSS

    Count if in the script to spot multiple values

      I have a website where people have to answer to a simple question:
      Let’s say: “What’s the total of 2+2?” and they have to leave their email address.
      Someone may make a mistake at the first attempt and try again. In that case he will have to resubmit the answer and insert his email address again.

      Now, as you can see in the script below, I have a list of email (Email Address) and a list of answers (your_answer)

      I am interested in understanding the email address that answered right at the first attempt, but due to  the way the data are structured I cannot do it, since I can track correct answers, wrong answers, but not correct first answers.

      Therefore I was thinking about creating a new column in the script called “Correct First Answers”.
      To do so, I was thinking about using the count if in the load script and create a logic like:

      If count [your_answer ] > 1 (aggregated by email address)
      à Recode  all your_answer associated to that email address to 19.

      This way, I will be able to separate email addresses that answered right from the email addresses that answered wrong, no matter if they answered right at the second, third attempt and so on.

      So, it’s kind of a pass / fail with just one attempt available.

      Here is the script related to the excel file I am uploading.
      Any help will be very appreciated.

      P.s. since I am quite new, please kindly do not use abbreviations or naming conventions or I may not be able to understand

      Many Thanks


      [thisismyTable]:

       

       

          LOAD

          date_submitted as ddate,

          time_submitted,

          ip_address,

          variant as "Landing Page Variant", //renaming

          page_uuid,

          name,

          email as "Email Address", //renaming to link two sheets

          your_answer,

          if (count(your_answer),[email])>1, 1, your_answer) as test,

        utm_source,

          utm_medium,

          utm_campaign,

          untitled,

          page_name,

          page_url,

          page_variant_name,

          untitled_menu,

          utm_content

          FROM [lib://Documents/client\All Javascript Leads (p).csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);


        • Re: Count if in the script to spot multiple values
          Sunny Talwar

          Try this:

           

          TableName:

              LOAD

              date_submitted as ddate,

              time_submitted,

              ip_address,

              variant as "Landing Page Variant", //renaming

              page_uuid,

              name,

              email as "Email Address", //renaming to link two sheets

              your_answer,

            utm_source,

              utm_medium,

              utm_campaign,

              untitled,

              page_name,

              page_url,

              page_variant_name,

              untitled_menu,

              utm_content

              FROM [lib://Documents/client\All Javascript Leads (p).csv]

          (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

           

          Join(TableName)

          LOAD if (count(your_answer),[email])>1, 1, your_answer) as test,

                    [Email Address],

                    name

          Resident TableName

          Group By [Email Address], name;

            • Re: Count if in the script to spot multiple values

              Hi,
              Thanks for your reply...unfortunately it doesn't work.
              I guess that it's a syntax issue, I am working now to fix it

               

               

              just a few points

               

              - Let's say that my table is named ThisIsTheTable

              - and let's say that I want to group the data just by Email Address (email is renamed as Email Address)


              Should I do smth like this?

               

              Join(ThisIsTheTable)  //the name of my table


              LOAD if (count(your_answer),[Email Address])>1, 19, your_answer) as test,  //19 as wrong result

                        [Email Address],    //the name of my column renamed

                   Resident ThisIsTheTable   //the name of my table

              Group By [Email Address];    //group by column renamed

               

              If it makes sense, I will try to fix the syntax

               

              Many Thanks,

               

              Paolo

                • Re: Count if in the script to spot multiple values
                  Sunny Talwar

                  Sorry I did not look at the count expression properly. Can you may be try this:

                   

                  TableName:

                      LOAD

                      date_submitted as ddate,

                      time_submitted,

                      ip_address,

                      variant as "Landing Page Variant", //renaming

                      page_uuid,

                      name,

                      email as "Email Address", //renaming to link two sheets

                      your_answer,

                    utm_source,

                      utm_medium,

                      utm_campaign,

                      untitled,

                      page_name,

                      page_url,

                      page_variant_name,

                      untitled_menu,

                      utm_content

                      FROM [lib://Documents/client\All Javascript Leads (p).csv]

                  (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                   

                  Join(TableName)

                  LOAD If(Count(your_answer) > 1, 1, 0) as test,

                            [Email Address],

                            name

                  Resident TableName

                  Group By [Email Address], name;


                  FinalTable:

                  LOAD *,

                            If(test = 1, test, your_answer) as new_your_answer

                  Resident TableName;

              • Re: Count if in the script to spot multiple values
                Andrew Walker

                Hi Paolo,

                From your table thisismyTable you could something try this:

                 

                TimeStampedAnswers:

                LOAD

                Timestamp#(ddate& ' ' & timesubmitted,'YYYY-MM-DD hh:mm:ss.fff') as AnswerTimestamp,

                [Email Address],

                your_answer

                Resident thisismyTable

                 

                 

                FirstAnswerTimeStamps:

                LOAD

                [Email Address]

                min(AnswerTimestamp) as AnswerTimestamp

                Resident TimeStampedAnswers Group by [Email Address]

                 

                Noconcatenate

                Left Keep(FirstAnswerTimeStamps)

                FirstAnswers:

                LOAD

                AnswerTimestamp,

                [Email Address]

                your_answer

                Resident TimeStampedAnswers; //could add Where clause here to exclude incorrect answers

                 

                DROP TABLE TimeStampedAnswers;

                DROP TABLE FirstAnswerTimeStamps;

                 

                The table rows in the table FirstAnswers can be checked for correct answers if you did not use a where clause where indicated.

                 

                Hope this is of interest. I've not been able to test this so I'll apologise now for any errors.

                • Re: Count if in the script to spot multiple values

                  Hi all,

                   

                  Thanks for your replies.
                  I managed to solve the problem without Scripting, just by creating the right Master Items

                   

                  1) Number of Attempts by Email Address

                   

                        aggr(count(your_answer), [Email Address])

                   

                   

                  2)  Developer Result

                      if(aggr(count(your_answer), [Email Address])=1, 'Pass', 'Fail')

                   

                   

                  It works! Now I have another issue...but I have submitted another question!

                   

                  Best Wishes

                   

                  Paolo