Can I use calculated field in dataflow to check for future date within array and update it to current date? | Community
Skip to main content
JoeAl1
JoeAl1
Level 2
August 30, 2024
Solved

Can I use calculated field in dataflow to check for future date within array and update it to current date?

  • August 30, 2024
  • 1 reply
  • 1279 views

I have a dataflow that was created a few months ago to ingest financial accounts data via batch ingestion. The account data is an array of various account fields (ie. account number, product, balance, etc). One of the fields in the array is a date indicating the last ACH deposit transaction date for funds deposited into the account. We recently found out that some of these dates can be future dates when is creating issues for journeys developed in AJO that leverage this date field. Is it possible to create a calculated field that checks every account being processed in the array accounts[*].lastTDHDirctDepositDate for a future date and replace it with a static date value of "0001-01-01". If the date isn't a future date then retain the value coming in from the source data. We can then map this calculated field to the field in the XDM schema thus ensuring we don't have future dates?

 

I was trying to use iif function 

iif(accounts[*].lastTDHDirctDepositDate > now(), "0001-01-01", accounts[*].lastTDHDirctDepositDate) but it seems I'm getting an error with the bracket so I'm assuming the iif function is looking for the array to be flattened but I'm not sure. 

 

I'm hoping someone here can assist?

 

Thanks

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

Hi @joeal1 ,

 

As it is an array I believe the number of elements can vary. Otherwise, if it was a fixed number of elements for every record you could have possibly extracted each element within if else block and reconstruct the array back for the elements passed the criterions but it would be an overkill and not a desired solution. Since AEP is not an ETL tool by design it is advisable to do those calculation at the source end before sending the data.

 

Regards,

Arijit Ghosh

1 reply

J
Adobe Employee
jainarundeepAdobe Employee
Adobe Employee
September 3, 2024

Hi @joeal1 ,

 

Unfortunately you cannot iterate through array using data prep functions.

 

Thanks,

@jainarundeep 

    JoeAl1
    JoeAl1Author
    Level 2
    September 4, 2024

    Thank you Arun for the reply. Are there any other possible solution options that can be applied within Adobe to solve for this? I know this can be done in the ETL outside Adobe when preparing the json data files for batch ingestion but was wondering if there are solution options within Adobe.

    JoeAl1
    JoeAl1Author
    Level 2
    September 9, 2024

    Hi @joeal1 ,

     

    As it is an array I believe the number of elements can vary. Otherwise, if it was a fixed number of elements for every record you could have possibly extracted each element within if else block and reconstruct the array back for the elements passed the criterions but it would be an overkill and not a desired solution. Since AEP is not an ETL tool by design it is advisable to do those calculation at the source end before sending the data.

     

    Regards,

    Arijit Ghosh


    Thank you Arigit!! I agree that this isn't optimal solution and thus I'm moving forward with addressing this in our ETL layer.

    Terms & ConditionsAccessibility statement

    Loading footer...