topic Re: Advanced Match formula in QlikView

Advanced Match formula

Thu, 05 Nov 2015 12:14:51 GMT

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

Re: Advanced Match formula

marcus_sommer — Thu, 05 Nov 2015 14:12:31 GMT

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

- Marcus

Re: Advanced Match formula

robert_mika — Thu, 05 Nov 2015 14:20:04 GMT

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;

Re: Advanced Match formula

Thu, 05 Nov 2015 15:02:59 GMT

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.

Re: Advanced Match formula

robert_mika — Thu, 05 Nov 2015 16:47:55 GMT

I had to use sample for an example.

Use the same code for your SQL load.