Construct Date in queryDef

Avatar

Avatar
Validate 10
Level 4
davidl14970702
Level 4

Likes

30 likes

Total Posts

93 posts

Correct reply

13 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile

Avatar
Validate 10
Level 4
davidl14970702
Level 4

Likes

30 likes

Total Posts

93 posts

Correct reply

13 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile
davidl14970702
Level 4

20-06-2018

The concept is based on our monthly subscription model product. The reasoning is the subscription model app is not indicating a non-renewed customer. So I need to look at the past 2 months (from GetDate()) to see the $ value to figure out if I need to treat the recipient as a customer or lost customer(non-renewed).

I need to pull back the most recent purchase month and log the transaction total for each recipient in my workflow.

For example:

Previous month 1 transaction total (1 month ago (0-31days ago))

Previous month 2 transaction total (2 months ago (32-60 days ago))

Basic Rules:

If Previous month 1 > 0 && Previous month 2 >= 0 that recipient is current customer.

if Previous month 1 == 0 && Previous month 2 > 0 that recipient is a non-renewed customer.

I've got most of this done BUT, im having trouble inserting the date var into the XML queryDef. And I'm also not sure if I am constructing the data correctly. The logInfo() seems to be returning the correct dates but Im not sure if the XML is reading it correctly. It does run but Im not returning any transactions when I should have something.

this is the line in my queryDef. datetSum1 is my variable from my date constructor (below, its toward the bottom).

<condition expr={"[@transactionDate] >= '" + dateSum1 + "'"}/>

//Get Previous Purchaee Month 1

    var previousPurchaserMonth1 = xtk.queryDef.create (

    <queryDef schema="lw:saleDetail" operation="select" >

    <select>

       <node expr="Sum(@transactionLineTotal)" alias="@sumPreviousPurchaserMonth1" />

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

    </select>

     <where>

         <condition expr={"[@LID] = '" + r.@LID + "'"}/>

         <condition expr={"[@productGroupSegment] = '" + '141' + "'"}/>

         <condition expr={"[productMaster/@productSubcatDesc] = '" + 'Ongoing-smallgroup.com' + "'"}/>

         <condition expr={"[@transactionDate] >= '" + dateSum1 + "'"}/>    //this is the line in question  

      </where>

    </queryDef>

    ).ExecuteQuery();

Replies

Avatar

Avatar
Validate 250
Level 10
pablo_rosero1
Level 10

Likes

81 likes

Total Posts

348 posts

Correct reply

77 solutions
Top badges earned
Validate 250
Validate 100
Validate 50
Validate 25
Validate 10
View profile

Avatar
Validate 250
Level 10
pablo_rosero1
Level 10

Likes

81 likes

Total Posts

348 posts

Correct reply

77 solutions
Top badges earned
Validate 250
Validate 100
Validate 50
Validate 25
Validate 10
View profile
pablo_rosero1
Level 10

27-11-2018

Hi David,

Please take a careful look at the Adobe Campaign Help | Advanced expression editing

I hope this helps!

All the best,

Pablo