Count if in the script to spot multiple values

[#1]

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

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

[effinty2112]

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.

[#4]

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

[#5]

Hi,

Thanks for the reply

It doesn't work unfortunately...I am trying to work it out now

Many Thanks

P

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

[#7]

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