Forum Discussion
Helper IVDaily Incremental Refresh with Delta Capture and Reporting
Helper IVDoes it matter if my data does not have an audit date time field? Unfortuantely, it doesn't look like any of the target tables have a "lastUpdated" or "LastModified" field exposed to the report layer export. So the only thing I have are exact snapshots each day of the same fields with a file name of the previous day's export and an appended 'snapshot date' field into each record of the target snapshot.
Super Userthe file date is sufficient.
- gemcityzach
Thank you. I am still kind of lost. I've read the EXCEPT, INTERSECT and a bunch of tutorials about combining two tables. But my problem is that I don't have tables or distinct queries, I have one query with appended data already in it. I.e., my single query has 03-01-2024 through 03-31-2024 data all essentially stacked on top of itself with a 'snapshot date' field.
It's escaping me how on a rolling daily basis I compare today's records to yesterday's records to identify any records that have changed since I don't have an audit field (lastModified).
- lbendlin
give me some sample data to work with and I can show you the mechanics. "Table" can be a physical thing, but it can also be your filter context, or a table variable.
- gemcityzach
You rock!
Day 1 Data
ID Name Target_Chg_Field Snapshot_Dt Product 1 John High 03-01-2024 Shoes 2 Mary Moderate 03-01-2024 Fabric 3 Sue Low 03-01-2024 Leather Day 2 Data
ID Name Target_Chg_Field Snapshot_Dt Product Value Changed 1 John Low 03-02-2024 Shoes No change 2 Mary Moderate 03-02-2024 Fabric No change 3 Sue High 03-02-2024 Leather No change 4 Zach Low 03-02-2024 Guitars No change Output table comparing Today to Yesterday (would be awesome if this was dynamic to any two given periods)
ID Name Target_Chg_Field Snapshot_Dt Product Value Changed 1 John Low 03-02-2024 Shoes High to Low 3 Sue High 03-02-2024 Leather Low to High 4 Zach Low 03-02-2024 Guitars New Record