7 Replies Latest reply: Nov 2, 2015 12:03 PM by Paolo Ferri RSS

    Scan a column and populate another column accordingly

      Hi All,

       

      Here is my question:

       

      I have a file with two columns, one is named ''email address'' and the other is named ''answers''
      They are related to a website where customers have to ask a question: what's the result of 3+2?
      Everytime they give the answer they have to put their email address, so it is possible to have a scenario like this:

       

      email address       answer

      Paolo                   4

      Paolo                   9

      Paolo                   5

      Rory                    5

      Luis                     5
      JP                       10

       

      In this scenario, I can clearly see the correct and the wrong answers, but what I am interested in is the Customers' First Answer
      So, what I need to do is to scan the email address column and see if there are duplicates. In this case, Paolo is a duplicate value, therefore I will assume that he gave more than one answer and it means that his First Answer was wrong.

       

      So, the logic of what I need to do is as follows:

       

      1) Create a new empty column called ''First Answer"

      2) Scan the "answer" column --> if there is 5, put CORRECT in the ''First Answer Column'', else, put WRONG

      3) Scan the "email address" column

      4) See if there are duplicates in the "email address column". If yes, put WRONG in the "First Answer Column" in the corresponding rows

      5) Filter out the duplicates in the email address column (and so filter out all the related rows)

       

      So the final output should be:

       

      email address       answer                                     First Answer
      Paolo                   nomatterwhatnumber                 Wrong

      Rory                    5                                              Correct
      Luis                     5                                              Correct
      JP                       10                                             Wrong

       

      I am a very beginner and considering that I think I have found the correct logic structure I would need hints on the script syntax

       

      Hope it's clear,
      Thank you in advance

        • Re: Scan a column and populate another column accordingly
          Massimo Grossi

          RESULT


          1.png


          SCRIPT


          Z:

          load *, AutoNumber(rowno(), email) as AnswerNum, rowno() as Id inline [

          email,      answer

          Paolo,                  4

          Paolo ,                  9

          Paolo  ,                5

          Rory    ,                5

          Luis    ,                5

          JP        ,              10

          ];

           

          Final:

          load

            email, answer,

            if(answer=5, 'Correct', 'Wrong') as FirstAnswer

          Resident Z

          Where AnswerNum=1;

           

          DROP Table Z;

            • Re: Scan a column and populate another column accordingly

              Hi Maxgro,
              Thanks for your reply.
              I still have a couple one questions though:

               

              I have already uploaded the whole file (where there are several other columns)

               

              LOAD

                  date_submitted,

                  time_submitted,

                  ip_address,

                  page_uuid,

                  email as "Email Address", //renaming

                  answer,   

                  page_url,

                  page_variant_name,

                  untitled_menu,

                  utm_content

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

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

               

              Could you please show me where I have to put the script?
              Please consider that I don't have only 5 email addresses but 500. Should I upload the entire table in the script?

               

              Many thanks,o

               

              Paolo

                • Re: Scan a column and populate another column accordingly
                  Massimo Grossi
                  Should I upload the entire table in the script?

                  No, I don't have your data so I make a small example with inline load

                  Try this; if you have problems, please post your csv, it would be more simple to answer

                   

                  YourTable:

                  LOAD

                  AutoNumber(rowno(), email) as AnswerNum,

                  //rowno() as Id,

                      date_submitted,

                      time_submitted,

                      ip_address,

                      page_uuid,

                      email as "Email Address", //renaming

                      answer,  

                      page_url,

                      page_variant_name,

                      untitled_menu,

                      utm_content

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

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

                   

                  Final:

                  load

                    [Email Address], answer,

                    if(answer=5, 'Correct', 'Wrong') as FirstAnswer

                  Resident YourTable

                  Where AnswerNum=1;

                   

                  DROP Table YourTable;



                    • Re: Scan a column and populate another column accordingly
                      Jared Papador

                      Since you have the date and time submitted you could also find the minimum of those by email address:

                       

                      Table1:

                      LOAD

                          Num(date_submitted) + Num(time_submitted) as SubmittedTimeStamp,

                          date_submitted,

                          time_submitted,

                          ip_address,

                          page_uuid,

                          email as "Email Address", //renaming

                          answer,   

                          page_url,

                          page_variant_name,

                          untitled_menu,

                          utm_content

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

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

                       

                      Left Join (Table1)

                      LOAD

                            "Email Address",

                            Min(SubmittedTimeStamp) as FirstSubmission

                      Resident Table1

                      Group By "Email Address";

                       

                      FinalTable:

                      NoConcatenate LOAD

                            *

                      Resident Table1

                      Where FirstSubmission = SubmittedTimeStamp;

                       

                      DROP TABLE Table1;

                       

                      Now you would be left with a table with just the earliest answer per email address (assuming they don't have multiple submissions at exactly the same time).

                        • Re: Scan a column and populate another column accordingly
                          Stefan Wühl

                          I think the solutions present are missing the requirement that duplicate answers should be handled as wrong, no matter what answer has been given first (even when Paolo asnwered 5 as first answer, the result should be WRONG, right?).

                           

                          INPUT:
                           
                          load * INLINE [
                            email, answer
                            Paolo, 4
                            Paolo , 9
                            Paolo , 5
                            Rory , 5
                            Luis , 5
                            JP , 10
                            ]
                          ;
                           
                          Final:
                           
                          LEFT JOIN (INPUT)
                          load
                          email,
                           
                          if(Only(answer)=5, 'Correct', 'Wrong') as FirstAnswer
                           
                          Resident INPUT
                          GROUP BY email;

                          This solution has one drawback, if there are duplicate answers with value 5, it will be rated Correct. IMHO, I can live with that, because there was a unique, unambiguous answer by the user.

                            • Re: Scan a column and populate another column accordingly
                              Massimo Grossi

                              yes, I added a check in my first script

                              Now Paolo is wrong and Test is wrong too

                               

                              Z:

                              load *, AutoNumber(rowno(), email) as AnswerNum, rowno() as Id inline [

                              email,       answer

                              Paolo,                   4

                              Paolo ,                  9

                              Paolo  ,                 5

                              Rory    ,                5

                              Luis     ,                5

                              JP        ,               10

                              Paolo,                   4

                              Paolo,                   4

                              Test, 5

                              Test, 5

                              ];

                               

                              left join (Z)

                              load email, count(answer) as AnswerCount

                              Resident Z

                              group by email;

                               

                              Final:

                              load

                                email, answer,AnswerCount,

                                if(answer=5 and AnswerCount=1, 'Correct', 'Wrong') as FirstAnswer

                              Resident Z

                              Where AnswerNum=1;

                               

                              DROP Table Z;

                    • Re: Scan a column and populate another column accordingly

                      Hi Guys,
                      Thank you all for your answer but I do not understand. I am a very beginner (I have started using click 5 days ago) and for me it's difficult to spot what has to be put in the script and what is your ''naming convention''.

                       

                      I am looking online to find explainations to the commands you use. I have seen that ''inline'' has to be used when you do not upload a file but you just want to write it in the script

                       

                      Please note that I have uploaded the file through the data load editor, and this is what I got

                       

                      // All Javascript Leads

                      LOAD

                          date_submitted,

                          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://Document...


                      Please comment everything that I should not put in the code but you are writing to give me tips (like Z, final, Resident, etc...)

                       

                      Thank you