Highlighted

UNIQUE Recipient Counts - when Grouped by "Something" - Using QueryDef

davidh2892249

05-04-2018

Hi There,

I have a requirement whereby I would like to use the queryDef JS to query a custom "answer log" schema, and then get unique counts of recipients grouped by "answer".

Example data

Schema: dev: poll_trackingLogs

Recipient ID (@recipient-id)               Answer (@answer)            LogDate(@logDate)

1,818,610                                              A                                          04/04/2018 12:00:00

1,818,610                                              B                                          04/04/2018 16:23:49

1,818,611                                              D                                          04/04/2018 16:46:26

1,818,611                                              D                                          04/04/2018 16:49:02

1,818,611                                              B                                          04/04/2018 16:49:42

1,818,610                                              B                                          04/04/2018 16:52:40

1,818,610                                              C                                          04/04/2018 16:52:48

1,818,640                                              C                                          04/04/2018 17:01:49

1,818,611                                              C                                          04/04/2018 17:03:16

1,818,610                                              C                                          04/04/2018 17:03:50

I've been using the generic query editor (as this will help be generate the XML that i will then use in a JS queryDef statement within a webApp).

I have been able to obtain unique counts per recipient per answer (by using Countdistinct() expression and grouping by @answer, as below).

But what I'd really like to be able to do, is get unique counts per recipient (whereby their first log is then counted against the answer, but subsequent logs are not counted).

I would expect the output of the above data to look like this:

Answer     Count

A               1           

B               0

C               1

D               1

This is my approach to getting per recipient per answer (but as mentioned, this is not quite what I need)

1458449_pastedImage_12.png

1458450_pastedImage_13.png

1458451_pastedImage_14.png

1458455_pastedImage_15.png

1458456_pastedImage_16.png

1458457_pastedImage_19.png

In turn, my QueryDef for this approach is this:

<% 

var query = xtk.queryDef.create( 

<queryDef  

schema="dev:poll_trackingLogs" operation="select">          

<select>          

    <node expr="@answer" groupBy="1" />

    <node alias="@count" expr="Countdistinct([@recipient-id])"

              /> 

</select>

<where> 

    <condition expr="@name = 'Tottenham''" />

</where>

<orderBy>

    <node expr="@answer" sortDesc="false"/>

</orderBy>                              

</queryDef> 

); 

var result = query.ExecuteQuery();

%> 

Replies

Highlighted
Highlighted

MarcelSzimonisz

MVP

18-04-2018

Hello,

sorry for being late

You need to select recipients of that survey then dedup them by recip ID to get unique recipients then add enrichment to add "Data linked to the filtering dimension" chose to retrieve very first  answer of given survey. Then in the same enrichment "edit additional data" select your answer field and check group by and add recip id to count it. I do not have any surveys data but this should work (hopefully)

1468359_pastedImage_10.png

1468338_pastedImage_2.png

1468339_pastedImage_3.png

1468355_pastedImage_4.png

1468357_pastedImage_7.png

Marcel

Highlighted

davidh2892249

18-04-2018

Hi Marcel,

This is fine when using a workflow... however I am doing this entirely within a WebApp (using queryDef).

So I therefore need to be able to query a schema and group by (and make unique at recipient ID) all within the query:

E.g.

Below is my current (not quite unique) example.

I was asking how it could be done using the generic query editor, simply because i'd be able to extract the XML i need from that approach.

Unfortunately I cannot use a workflow dedupe activity in this situation.

Thanks

David

<%

var query = xtk.queryDef.create(

<queryDef 

schema="dev:poll_trackingLogs" operation="select">         

<select>         

    <node expr="@answer" groupBy="1" />

    <node alias="@count" expr="Countdistinct([@recipient-id])"

              />

</select>

<where>

    <condition expr="@name = 'Tottenham''" />

</where>

<orderBy>

    <node expr="@answer" sortDesc="false"/>

</orderBy>                             

</queryDef>

);

var result = query.ExecuteQuery();

%>

Highlighted

MarcelSzimonisz

MVP

18-04-2018

Hello,

Ok then select all answers for Tottenham and loop over all results and keep only oldest answer per recipient id. You can delete row you do not need by row.delete in your loop condition based on eventDate

or you can try usiing sqlSelect() command by selecting somthing similar at this link Finding the Oldest/Youngest Records Within a Group

var res = sqlSelect("publicUrl,@sstringValue:string",

  "select sstringValue from XtkOption where sname='XtkFileRes_PublicUrl'")

logInfo(res.publicUrl.@sstringValue.toString())

Marcel

Highlighted

davidh2892249

18-04-2018

Thanks Marcel,

Not sure i can use sql select in a webapp?!?

I don't suppose you would know how to edit my XML above to get the desired outcome?

Thanks

David

Highlighted

MarcelSzimonisz

MVP

18-04-2018

Hello,

i can sketch one posible solution for you

function onlyUnique(value, index, self) {

   return self.indexOf(value) === index;

}

var res = select unique recipient ids belonging to that survey

var possibleAnswers = [];

for each (var item in res){

     resOldestAnswer = select very first recipient annswer by given id

     res.@answer = resOldestAnswer.@answer;

     res.@eventDate = resOldestAnswer.@answer;

     possibleAnswers.push(res.@answer.toString());

}

//get unique anwers if you know them you can populate array manually..

var posibleAnwers = possibleAnswers.filter(onlyUnique);

var answers = [];

var answer = {};

for each (var posibleAnswer in posibleAnwers ){

     answer.count = res.(answer==possibleAnswer).length();//if length() does not work use Object.keys(res.(answer==possibleAnswer)).length

     //the whole thing res.(answer==possibleAnswer) might not work as i never tried it just took it from jsapi documentation

     answer.label = posibleAnwer;

     answers.push(answer);

     answer = {};

}

//hope you do not have over 10k records 🙂

Marcel