0

I have a journey in which entry criteria is Salesforce Data. Whenever a contact enters into a journey, the journey puts its details into a DE(created by the journey itself). I want to write automation to delete the records which are 90 days older in that DE. While writing SQL, I am getting an error as the DE created by the journey contains special characters(:,;) in the field name.

Renaming the field name is also not an option as it makes us lose data from the journey.

Is there any workaround or an alternate solution for this?

| improve this question | |
  • can you add more details about your query and eventually post it here? – Bechir Nov 3 '19 at 10:22
  • It is a simple query like Select * from DE where Inquiry__c:Lead__r:Email = "XX@XX.com" the colon is the problem in the query – Ankit Bansal Nov 3 '19 at 18:06
  • 2
    @AnkitBansal have you tried wrapping the column names with [] in your query? for example Select * from DE where [Inquiry__c:Lead__r:Email] = "XX@XX.com" ? – zuzannamj Nov 3 '19 at 18:23
  • Your suggestion worked. Thanks @zuzannamj – Ankit Bansal Nov 5 '19 at 19:09
  • No worries, I will update my answer, to reflect this. – zuzannamj Nov 5 '19 at 19:11
4

EDIT

If you are querying a Data Extension, which has column names with colons, semicolons, commas or spaces, try wrapping the column names with square brackets []. For example:

Select * 
from DE
where [Inquiry__c:Lead__r:Email] = "XX@XX.com"

No need to set up an automation.

Instead, go to Contact Builder > Data Extensions, find your journey DE and turn on Data Retention:

enter image description here

And set it to delete individual records after 90 days or any other interval you need:

enter image description here

Read more here: Data Retention

| improve this answer | |
  • Thanks for the reply. I need to add an extra filter on the DE. Only the records which have source = "Internet" should only be deleted and not all. – Ankit Bansal Nov 3 '19 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.