Hi All,
We have a requirement, where we have to import excel file data into Appian database stage table. Once data is loaded into stage table we have to validate the date format for date columns whether the date is invalid or valid using query entity or any custom function. In-valid records should be removed and only valid records be there. Can someone help us with this kind of scenario in Appian how we can avoid the in-valid date rows in Appian.
Regards,
Dheeraj G
Discussion posts and replies are publicly visible
There are severl ways to do this.. you can create en expression rule in order to validate date values and use it when importing th data, or once imported and before load it in the interface...
hi dheerajg0003 you can have some SP to check and update your date format after once pushing the data into a staging table.example code snippet:
UPDATE staging_table SET is_valid_date = CASE WHEN STR_TO_DATE(date_column, '%Y-%m-%d') IS NOT NULL THEN 1 ELSE 0 END;
Another method that does not require a stored proc would simply to have a process that reads the staging table in batches and then either updates the staging table or inserts the valid rows into another table.
I will verify the alternative method as well
Hi Abhishek,
Thank you for your suggestion, If the date is Invalid then only it should be flagged as 0, it should not update the rows which has existing null value.