Forum Discussion
Resolver IIDate Difference between rows in single table, but matching data links in different columns
Resolver IIv-yuta-msft - As indicated in the screen shot, I need to find the date difference between the original invoice date and the last receipt date that clears the balance to zero. The data table link between these two rows will be the Apply_ID. This will provide us with the timeliness of collection and timeliness of debtor payment to clear the balance.
Please see the screen shot. Thanks.
Resolver II
The logic of split into four tables -
The data originates from a single general ledger transaction table, containing all the different transaction types linked to the GL. In my first successful attempt at solving this, I have duplicated this single table into four tables and filtered each of these tables in the query stage by transaction type, thus, creating four separate tables -
Transaction types/resulting tables:
1. invoices
2. receipts
3. purchases
4. payments
This has made it easier to calculate the difference between the transactions dates since no calculations are required to link the transactions in the single table by Apply_ID. The link is made directly in the Manage Relationships process.
However, this requires loading the transaction table 4x during the query stage and I am wondering 2 things, as mentioned above:
1. can this be done within only 1 table?
2. is there a performance benefit of loading only 1 table, or does loading 4 tables originating from the same table OK?
2a. currently there are about 600K lines in the GL table. Over time, as that increases, I assume loading it 4x will reduce performance.
Hope this helps. Thanks.