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

[davidh2892249]

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

%> 

[davidh2892249]

Appreciate this might be a tricky one....

Any ideas

@florentlb

@Jean-Serge Biron

@Vipul Raghav

@marcel.gent.86

[Marcel_Szimonisz]

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]

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]

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]

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]

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