Save the MAX eventDate of nms:broadLogRcp | Community
Skip to main content
salvdangelo
salvdangelo
Level 5
Dec 19, 2017 2:05 PM UTC

Save the MAX eventDate of nms:broadLogRcp

Hi,
I'm trying to get the MAX value of @eventDate from Recipient delivery logs (nms:broadLogRcp)
I tried this option but vars.maxBroadLogEvent output is always empty.

var query = xtk.queryDef.create( 

  <queryDef schema="nms:broadLogRcp" operation="select"> 

    <select> 

      <node expr="@eventDate"/> 

    </select>

    <orderBy>

     <node expr="@eventDate" sortDesc="true"/>

    </orderBy>

  </queryDef> 

); 

result = query.ExecuteQuery(); 

vars.maxBroadLogEvent = result[0].@eventDate;

logInfo("MAX BROADLOG EVENT: "+ vars.maxBroadLogEvent);

 

for each (var e in result) { 

 

logInfo(e.@eventDate); 

 

}

Can you identify the issue?

Jean-Serge Biron​
@nkur​
Adhiyan​
kirti.rawat​
Adobe Campaign​

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

5 replies

saikatk2447661
saikatk2447661
Level 4
Jan 8, 2018 6:20 AM UTC

Hi Salvdangelo,

If there is any delivery log with status ignored, there will be no eventdate but it will come on top of the list. To mitigate this issue use the following syntax.

<select>

      <node expr="@eventDate"/>

    </select>

   <where>

      <condition expr="@eventDate is not null"/>

    </where>

    <orderBy>

     <node expr="@eventDate" sortDesc="true"/>

    </orderBy>

Thanks

Saikat

Translate
david--garcia
david--garcia
Level 10
Jan 2, 2018 1:29 PM UTC

The following is another way of querying the max value for whatever column

maxId = sqlSelect("Records,@id:string","SELECT MAX(ideliveryId) FROM Neolane.Neolane.NmsrtEvent");
logInfo(maxId.Records.@id);

In your case, something like the following;

maxEventDate = sqlSelect("Event,@maxDate:string","SELECT MAX(tsEvent) FROM Neolane.Neolane.NmsbroadLogRcp");//replace neolane with your database name.schema
logInfo(maxEventDate.Event.@maxDate);

Depending on the amount of records in this table, it could take a while for the query to run. my db has more than 40m records in here.

Translate
florentlb
florentlb
Level 10
Jan 2, 2018 12:08 PM UTC

Hi,

Were you able to figure out why you got this issue?

Let us know,

Florent

Translate
Jean-Serge_Biro
Jean-Serge_Biro
Level 10
Dec 19, 2017 2:43 PM UTC

Hi Salvatore,

Your code works perfectly on my instance.

Please check if your delivery mapping is using nms:broadlogrcp, perhaps are you using a specific one?

Please use the Generic query editor tool to have a look on the broadlogrcp content, and especially the eventDate column, to be sure that it is the workflow behavior that is wrong.

Then add another column  in your loop to debug or for [0] record to be sure as well.

Regards

JS

Translate
    Jean-Serge_Biro
    Jean-Serge_Biro
    Level 10
    Dec 19, 2017 2:50 PM UTC

    PS: if you intend to use Message Center messages, the delivery mapping is nms:broadlogRcpEventHisto

    Translate
    Terms & ConditionsAccessibility statement

    Loading footer...