Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Ignore certain duplicate lines in accumulation

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
3 Replies
ahaahaaha
Honored Contributor

Re: Ignore certain duplicate lines in accumulation

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

Not applicable

Re: Ignore certain duplicate lines in accumulation

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

Not applicable

Re: Ignore certain duplicate lines in accumulation

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