Qlik Sense – Duplicate Invoice Check Between Yesterday Data and 18-Month Historical Data Based on Dynamic Criteria (DP15, DP34, etc.)

[sachin1]

Hi Everyone,

I am working on a duplicate invoice validation use case in Qlik Sense and need guidance on how to design the script, data model, and UI selections.

Data Description

I have two datasets:

1. 18-Month Historical Data (Already Processed Invoices)

| VendorCode | InvoiceDate | InvoiceAmount | PONumber | InvoiceNumber |
| ---------- | ----------- | ------------- | ---------- | ------------- |
| 178221 | 11/22/2024 | 1833012 | 4509942482 | NG/684 |
| 372841 | 11/25/2024 | 4601 | 4510564662 | 24-25/S/382 |
| 391239 | 11/4/2024 | 75000 | 4510560616 | BPV/04 |
| 363391 | 11/7/2024 | 7788 | 4510535836 | TNC/24-25/593 |

2. Yesterday Data (Invoices to Be Processed)

| VendorCode | InvoiceDate | InvoiceAmount | PONumber | InvoiceNumber |
| ---------- | ----------- | ------------- | ---------- | --------------- |
| 133846 | 10/19/2023 | 1525088.26 | 4509752128 | ET/2023-24/042 |
| 316440 | 4/29/2025 | 33134 | 4510765120 | 10003JI11137595 |
| 136893 | 7/15/2025 | 36958 | 4510975915 | 2.52627E+12 |
| 363391 | 11/7/2026 | 77886 | 10535836 | TNC/24-25/593 |

Requirement

Before processing yesterday’s invoices, I need to ensure that the same invoice is not processed twice.

To identify duplicates, we have multiple duplicate-check criteria (DP combinations) based on the following fields:

1. VendorCode

2. InvoiceDate

3. InvoiceAmount

4. PONumber

5. InvoiceNumber

Examples of DP criteria:

• DP15 → VendorCode + InvoiceNumber

• DP34 → InvoiceDate + PONumber

• DP123 → VendorCode + InvoiceDate + InvoiceAmount

• DP124

• DP1345

• DP12345

Expected Behavior

• In the Qlik Sense UI, I want a filter or selection (DP selector) such as DP15, DP34, DP12345, etc.

• For example, if I select DP15, the app should:

  • Compare Yesterday data with 18-month data using
    VendorCode + InvoiceNumber

  • If a match is found, return only the matching records from Yesterday data

Example Output (DP15 Selected)

| VendorCode | InvoiceDate | InvoiceAmount | PONumber | InvoiceNumber |
| ---------- | ----------- | ------------- | -------- | ------------- |
| 363391 | 11/7/2026 | 77886 | 10535836 | TNC/24-25/593 |

(Because VendorCode 363391 and InvoiceNumber TNC/24-25/593 already exist in the 18-month data.)

[Vegar · Accepted solution]

I suggest two ways forward with this. Take a look at my script and output below. 

1. Yellow marking: in line with your suggestion. It will give you the combination of duplicate type, in your case  VendorCode and InvoiceNumber where equal, hence DP15 on that row, and DP on the rest. 

2. Red marking: instead of classing , the value you could add a field for each dimension that you want to compare and set the value to yes/true when a duplicate value exists, and no/false when it is unique. It will give you a more dynamic approach filtering out the invoices instead having fixed categories. 

[sachin1]

The data is stored in an Excel file. The logic works correctly when using INLINE data; however, when the same logic is applied to the Excel file, it does not work as expected.