DataFlow - Quickbooks - Picking up Edits
Good Morning, Experts!
I wanted to resurrect a question I had asked previously.
The question encompassed a dataflow that was built. In the data flow, the data flow is bringing in inputs from 4 separate Stacker jobs that aggregate data from dataflows from various Quickbooks (QB) jobs (Accounts, Vendors, Class and GL). In this master data flow, there is a rank and window taking place that is accounting for Date, ModifiedTime and TXNumber in descending fashion. Where we see a discrepancy in our data is with Balance Sheet Month details report. What our source of truth shows on QB is that some months are off for Total Cash, Total Accounts Payable, Total Credit Cards and Total Liabilities, while some months are spot on to the penny. What we have noticed is that this happens when there is a modification done to the entry during reconciliation monthly of the books.
I have provided a screenshot of the rank and window items being looked at for the data.
We have hit a wall because at this point we do not see the data missing, it is there, but the Account Ending Balance portion is not being displayed correctly due to out of sequence information. I am trying to see if there is knowledge of any other field that we can use to rank and window by or express logic differently to get it to spot on. There is a TxnID field but it seems like a ROW GUID and does not seem like it would help us. However, I could be wrong and on to something, but, I am hopefully relying on your expertise and help.
Let me know if I can answer any questions or provide more feedback.