Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Advanced Match formula

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.

   

PersonReferensNrInvoiceDatePrintedInvoiceAmountInvoiceType
Mark8260172015-10-22 07:47No50NotPaid
Karl8260182015-10-22 07:47No1235Paid
Karl8260182015-10-29 07:49No1235NotPaid
Karl8260222015-10-22 07:48No50NotPaid
Dan8228392015-10-13 16:20No400NotPaid
Dan8228392015-10-13 16:20No400Paid
Lisa8278582015-10-27 13:38No15NotPaid
Lisa8291842015-10-30 09:25No4353NotPaid
Lisa8291842015-10-30 09:25No4353Paid
Jushua8185862015-10-01 10:29No510NotPaid
4 Replies
MVP & Luminary
MVP & Luminary

Re: Advanced Match formula

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

- Marcus

Re: Advanced Match formula

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;

Not applicable

Re: Advanced Match formula

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

I had to use sample for an example.

Use the same code for your SQL load.