Ignore certain duplicate lines in accumulation

[#1]

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

Measure	Selection
TRX Date	02/01/2017

Table 1 - source data

Jrnl_number	TRX Date	Nominal Code	Amount	LineRef
1	28/12/2016	9999	1	Normal Posting
2	29/12/2016	1111	16	Normal Posting
3	30/12/2016	9999	2	Normal Posting
4	31/12/2016	9999	15	Normal Posting
5	31/12/2016	9999	18	Balance Brought Forward
5	31/12/2016	9999	18	Balance Brought Forward
6	31/12/2016	9999	13	Delayed posting
6	31/12/2016	9999	13	Delayed posting
7	01/01/2017	9999	20	Normal Posting
7	01/01/2017	9999	20	Normal Posting
8	02/01/2017	9999	8	Normal Posting

Table 2 - manual mapping table (attached Excel file)

Nominal code	Group
1111	Group 1
9999	Group 2

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

Group	Amount
Group 2	79

[ahaahaaha]

Hi Amir,

As variant soluttion in attached file QVF. All comments you find in expression [Amount] of table "Final table" on the Sheet1 app. To view the comments, open expression [Amount] of table "Final table" in the expression editor.

Regards,

Andrey

[#3]

Hi Andrey,

Thanks for the rapid response.

However, if I filter to 31.01.2017 the Group 2 amount is displayed as 145.

It looks like the Final Table is omitting Jrnl_number 6.

Also, since you've added a set of 'Balance Brought Forward' lines (Jrnl_number 13), the following journals (14, 15, and 16) are considered delayed (according to our database's logic) since they are within the same year but after the 'Balance Brought Forward' lines.

As such, I've changed the data a slight bit and attached.

Regards,

Amir

[#4]

Hi Andrey, any chance you've been able to figure this one out?