Forum Discussion

JRHans09's avatar
JRHans09
Icon for Resolver II rankResolver II
7 years ago

Date Difference between rows in single table, but matching data links in different columns

I have searched for similar posts, but have not found one that exactly matches this scenario.   We have a single data table on the database holding all the General Ledger transactions. I have split...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
7 years ago

JRHans09 ,

 

"

1. is it possible to just load 1 instance of the transaction table and use measures to calculate the date difference between invoice and receipt dates, as well as purchase and paid dates?

2. OR, is it best to separate the transaction table into 4 tables - invoices, receipts, purchases, and payments?

"

 

Could you share the logic of date difference?(e.g.: Based on some specific condition) In addtion, could you clarify the logic of split into 4 tables?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • JRHans09's avatar
    JRHans09
    Icon for Resolver II rankResolver II
    7 years ago

    v-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.

    • JRHans09's avatar
      JRHans09
      Icon for Resolver II rankResolver II
      7 years ago

      v-yuta-msft  -

       

      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.