Advanced Match formula

[#1]

I need help with a advanced match formula

If  a Person's InvoiceAmount is a match in table InvoiceType "NotPaid" and 'Paid' the invoice should not show

so if person has a paid and unpaid amount that match. This is a paid invoice therefore it should not be shown I the table.

Person	ReferensNr	InvoiceDate	Printed	InvoiceAmount	InvoiceType
Mark	826017	2015-10-22 07:47	No	50	NotPaid
Karl	826018	2015-10-22 07:47	No	1235	Paid
Karl	826018	2015-10-29 07:49	No	1235	NotPaid
Karl	826022	2015-10-22 07:48	No	50	NotPaid
Dan	822839	2015-10-13 16:20	No	400	NotPaid
Dan	822839	2015-10-13 16:20	No	400	Paid
Lisa	827858	2015-10-27 13:38	No	15	NotPaid
Lisa	829184	2015-10-30 09:25	No	4353	NotPaid
Lisa	829184	2015-10-30 09:25	No	4353	Paid
Jushua	818586	2015-10-01 10:29	No	510	NotPaid

[marcus_sommer]

Why not using a listbox with the field InvoiceType and then select  'NotPaid'?

- Marcus

[robert_mika]

data:

LOAD Person&ReferensNr as Key,

* INLINE

[

    Person, ReferensNr, InvoiceDate, Printed, InvoiceAmount, InvoiceType

    Mark, 826017, 2015-10-22, No, 50, NotPaid

    Karl, 826018, 2015-10-22, No, 1235, Paid

    Karl, 826018, 2015-10-29, No, 1235, NotPaid

    Karl, 826022, 2015-10-22, No, 50, NotPaid

    Dan, 822839, 2015-10-13, No, 400, NotPaid

    Dan, 822839, 2015-10-13, No, 400, Paid

    Lisa, 827858, 2015-10-27, No, 15, NotPaid

    Lisa, 829184, 2015-10-30, No, 4353, NotPaid

    Lisa, 829184, 2015-10-30, No, 4353, Paid

    Jushua, 818586, 2015-10-01, No, 510, NotPaid

];

  join

temp:

load

Person&ReferensNr as Key,

count(Person&ReferensNr) as COUNT

Resident data

Group by Person&ReferensNr;

load

Key,

Person AS pERSON,

ReferensNr AS NUM,

InvoiceDate AS INVD,

Printed AS PRI,

InvoiceAmount AS INA,

InvoiceType AS INT,

COUNT

Resident data

where COUNT=1;

DROP TABLE data;

[#4]

Hi Robert I cant do a inline load because my real data is loaded from a SQL database. The data I've published is test data based on data from the SQL database.

[robert_mika]

I had to use sample for an example.

Use the same code for your SQL load.