3 Replies Latest reply: May 15, 2017 3:43 AM by Amir Jaber RSS

    Ignore certain duplicate lines in accumulation

    Amir Jaber

      We have a database where some of the data is a bit poor and needs to be corrected when displayed. Table 1 shows the source data, table 2 shows a mapping (done via an excel spreadsheet in the load script), and the last table shows what the result should be according to these rules:

      • All lines where LineRef = 'Balance Brought Forward' should be ignored
      • All lines where the Jrnl_number is larger than the max Jrnl_number where the LineRef = 'Balance Brought Forward' needs to ignore any duplicates
        • Reasoning: The database creates a duplicate of any posting (e.g. Jrnl_number 6) which comes in after Balance Brought Forward postings (Jrnl_number 5). This means any delayed posting gets counted twice.
      • However, all duplicate lines where the JnL number is not after a 'Balance Brought Forward' in the same year (as is the case with Jrnl_number 7) are to be included in full - i.e., the duplicates are both accepted.

       

      I hope I have covered everything but let me know if something needs clarified


      Filter pane selection

      MeasureSelection
      TRX Date02/01/2017

       

      Table 1 - source data

      Jrnl_numberTRX DateNominal CodeAmountLineRef
      128/12/201699991Normal Posting
      229/12/2016111116Normal Posting
      330/12/201699992Normal Posting
      431/12/2016999915Normal Posting
      531/12/2016999918Balance Brought Forward
      531/12/2016999918Balance Brought Forward
      631/12/2016999913Delayed posting
      631/12/2016999913Delayed posting
      701/01/2017999920Normal Posting
      701/01/2017999920Normal Posting
      802/01/201799998Normal Posting

       

      Table 2 - manual mapping table (attached Excel file)

      Nominal codeGroup
      1111Group 1
      9999Group 2

       

      Account 9999 balance as at end of selected date (02/01/2017)

      GroupAmount
      Group 279