Get data from temporal table in javascript node

[LAR1985]

Hello community,

I have a workflow in which I have added a first query node on the targenting dimension BroadLogRcp and in additional data I have added two calculated fields:

• one of date on event date to which I have applied the ToDate function to keep only with date, without time
• Another field to count on the records, in this way the number og contacts that have been made per day.

Next, I want to add a javascript node, to look for the date with the highest number of records and save in an instance variable both the date and the number of records, how can it be done?

Thanks in advanced

[ParthaSarathy · Accepted solution]

Hi @LAR1985 ,

Try the below script in JavaScript activity after Query,

var query = xtk.queryDef.create(
  <queryDef schema={vars.targetSchema} operation="select" lineCount="1">
    <select>
      <node expr="@toDateField"/>
      <node expr="@countField"/>
    </select>
	<orderBy>
      <node expr="@countField" sortDesc="true"/>
    </orderBy>
  </queryDef>
);
var result = query.ExecuteQuery();

for each (var record in result) {
instance.vars.eventDate = record.@toDateField;
instance.vars.count = record.@countField;
}

In the place of schema={vars.targetSchema}, you can also replace with your temp schema name, example: schema="temp:query"

If you have more than 10K inbound records, then use a split activity after query, limit the count to 1 and sort it based on count attribute and use this JavaScript activity next to split activity.

[LAR1985]

Thank you,

As those fields are calculated, I understand that I have to change in the code, as you comment, the target schema by the temp schema.

var query = xtk.queryDef.create(
<queryDef schema="temp:query" operation="select" lineCount="1">
<select>
<node expr="@toDateField"/>
<node expr="@countField"/>
</select>
<orderBy>
<node expr="@countField" sortDesc="true"/>
</orderBy>
</queryDef>
);
var result = query.ExecuteQuery();

for each (var record in result) {
instance.vars.eventDate = record.@toDateField;
instance.vars.count = record.@countField;

}

The problem is that it is failing "XTK-170036 Unable to parse expression '@countField'"., as I think that the name of the fields is correct (I include screenshot), I imagine that the problem is the name of the temporary schema, how can I see it? 

Thanks

[ParthaSarathy]

Hi @LAR1985 , In Alias > have @ in prefix. Replace countField with @countField and replace toDateField with @toDateField