Create audience from API call gives me Token Recognition error

[GabrielaNa1]

Hi,

I am trying to create an audience in AEP using API to get profiles that have a certain date attribute before or on a certain date attribute +30 days.

I am using this as the API body:;

-d '{
"name": "LatestCQdt lte LatestDD + 30",
"profileInstanceId": "AEPSegments",
"description": "Latest Cheque Redeem Date is before or on Most Recent Drop Date + 30 days",
"type": "SegmentDefinition",
"expression": {
"type": "PQL",
"format": "pql/text",
"value": "_myTenant.abnCalculatedAttributes.LastChequeRedeemedDate <=
_myTenant.abnCalculatedAttributes.Most_Recent_Drop_Date_DM + INTERVAL '\''30 days'\'' "
},
"schema": {
"name": "_xdm.context.profile"
}
}'

but it gives me this error:

{
"code": "400",
"message": "Error in parsing PQL expression \"_myTenant.abnCalculatedAttributes.LastChequeRedeemedDate <= _myTenant.abnCalculatedAttributes.Most_Recent_Drop_Date_DM + INTERVAL '30 days' \": line 1:130 token recognition error at: '''"
}

It is probably because of the wrong syntax for INTERVAL function, but I cannot find anywhere how to use this function and what is, in fact the syntax. I want to just add 30 days to the date.

Any suggestions?

[Asheesh_Pandey]

@GabrielaNa1  As I know, AEP's PQL currently doesn't support standard SQL-style interval expressions. You may want to calculate prior and store the mostRecentDropDateDM + 30 days as a new field. Another option is to try using date_diff() for comparing days between two date attributes.

[GabrielaNa1]

HI,

The INTERVAL syntax was suggested from Adobe support. I cannot find any reference on the Internat to INTERVAL in connection to PQL, or date_diff (the one you suggested) for that matter. Do you know where to find the complete reference for Adobe PQL, something that will give more details on this variant of SQL?

[brekrut]

Hello @GabrielaNa1 

I would recommend reviewing the following page for date formatting functions within the PQL syntax for segmentation. 

https://experienceleague.adobe.com/en/docs/experience-platform/segmentation/pql/datetime-functions

The interval function typically is used a function within the Adobe Experience Platform datalake.

I would recommend using the using the operations of <= against the fields on in your statement and use the occurs function.

https://experienceleague.adobe.com/en/docs/experience-platform/segmentation/pql/datetime-functions#o...

[Parvesh_Parmar]

Hi @GabrielaNa1,

I checked how the UI Audience Builder handles conditions for date-time type attributes. It seems that it does not allow using dynamic variables on both sides of the comparison. Typically, it only supports a direct date value or a fixed offset (e.g., a set number of days).

In your case, since you're comparing one date attribute to another plus a dynamic offset (+30 days), it might be causing the issue. I'm not entirely sure if this is a limitation, but it’s worth checking with Adobe Support to confirm whether PQL allows this kind of expression.

Hope this helps!

Kind regards,
Parvesh

[GabrielaNa1]

Thanks, Parvesh, 

the syntax I used, with INTERVAL and a dynamic offset, was suggested by Adobe Support. Which makes it even more frustrating that is not working.

I will press on Adobe support for more clarification.