Forum Discussion
Solved
Remove & add certain data in load
Hello All, On a daily base I get an excel extract from the past week. What I then need to do is remove the past 6 days from my existing data file and paste the 7 days I received in the latest ex...
- Ingest all files using the combine function, but make sure some relevant file-level date stamp remains (it auto-generates a "remove other columns" step before the expand, you can edit this).
- Reference this table into another table. Keep only the file-level date stamp and the by-row date stamp.
- Group-by the by-row-level date, aggregate max file-level date. This gives you a list: for every data date X, which file Y contains the most most recent version.
- Inner merge this resulting list on both dates. This will drop anything that's not in your list of selected rows.
Coding the extract date into the file name and then parsing it out is more reliable, as certain operations can futz with both the created and modified dates.
- Ingest all files using the combine function, but make sure some relevant file-level date stamp remains (it auto-generates a "remove other columns" step before the expand, you can edit this).
- Reference this table into another table. Keep only the file-level date stamp and the by-row date stamp.
- Group-by the by-row-level date, aggregate max file-level date. This gives you a list: for every data date X, which file Y contains the most most recent version.
- Inner merge this resulting list on both dates. This will drop anything that's not in your list of selected rows.
Coding the extract date into the file name and then parsing it out is more reliable, as certain operations can futz with both the created and modified dates.