topic Count if in the script to spot multiple values in QlikView

Count if in the script to spot multiple values

Thu, 03 Dec 2015 16:38:53 GMT

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 — Thu, 03 Dec 2015 18:24:37 GMT

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

effinty2112 — Fri, 04 Dec 2015 09:10:55 GMT

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

Fri, 04 Dec 2015 12:07:20 GMT

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

Fri, 04 Dec 2015 12:08:34 GMT

Hi,

Thanks for the reply

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

Many Thanks

Re: Count if in the script to spot multiple values

sunny_talwar — Fri, 04 Dec 2015 14:45:19 GMT

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

Fri, 04 Dec 2015 17:40:58 GMT

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