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

[JoeAl1]

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

[arundeepjain]

Hi @JoeAl1 ,

Unfortunately you cannot iterate through array using data prep functions.

Thanks,

@arundeepjain 

[JoeAl1]

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.

[Rite18]

@JoeAl1 it would be good to keep Adobe layer light [minimal transformation logic ] and keep this kind of implementation data @ETL layer during data prep process 

[JoeAl1]

That makes sense. Thank you!!

[arijitg · Accepted solution]

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

[JoeAl1]

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