UNIQUE Recipient Counts - when Grouped by "Something" - Using QueryDef | Community
Skip to main content
davidh2892249
davidh2892249
Level 5
April 5, 2018

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

  • April 5, 2018
  • 3 replies
  • 11219 views

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)

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

%> 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

3 replies

davidh2892249
davidh2892249Author
Level 5
April 18, 2018

Appreciate this might be a tricky one....

Any ideas

@florentlb

@Jean-Serge Biron

@Vipul Raghav

@marcel.gent.86

Marcel_Szimonisz
Community Advisor
Marcel_SzimoniszCommunity Advisor
Community Advisor
April 18, 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)

Marcel

davidh2892249
davidh2892249Author
Level 5
April 18, 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();

%>

Marcel_Szimonisz
Community Advisor
Marcel_SzimoniszCommunity Advisor
Community Advisor
April 18, 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

davidh2892249
davidh2892249Author
Level 5
April 18, 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

Marcel_Szimonisz
Community Advisor
Marcel_SzimoniszCommunity Advisor
Community Advisor
April 18, 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

Terms & ConditionsAccessibility statement

Loading footer...