Field1, Field2 from separate tables not exist

[sjhussain]

We are working on an A/R dashboard in Sage ACCPAC and are having an issue to separate Invoices and Payments.

The main INVOICES and PAYMENTS are in one table and are identified by IN/CA

ALL_TABLE:

INVOICE_TYPE, INVOICE_ID, PAYMENT_REF

IN, IN001, PY001

IN, IN002,PY002

IN, IN003, PY003

CA, PY001. IN001

CA PY004,

CA PY005,

What we want to do is separate the Invoices and Payments and then concatenate them back.

The problem we are facing is that we do not want to include the Payments which have already been attached to an Invoice.

For example PY001 is PAYMENT_REF as well as INVOICE_ID.  It is duplicated.  So in the PAYMENT_TABLE we do not want to include this

INVOICE_TABLE:

LOAD INVOICE_ID as INV_ID,

PAYMENT_REF

RESIDENT ALL_TABLE

WHERE INVOICE_TYPE = 'IN'

PAYMENT_TABLE:

LOAD INVOICE_ID, PAYMENT_REF

RESIDENT ALL_TABLE

WHERE INVOICE_TYPE = 'CA' and NOT EXISTS (INV_ID, PAYMENT_REF)

Now how do we concatenate them into one table? We tried using not exists but it seems that we are not using it properly.

Will appreciate if someone can assist us.

Thanks.

[Mark_Little]

Hi,

Check the attached document but is this what you want?

INVOICE_TYPE	INVOICE_ID	PAYMENT_REF
IN	IN001	PY001
IN	IN002	PY002
IN	IN003	PY003
CA	PY004
CA	PY005

ALL:

LOAD * INLINE [

INVOICE_TYPE, INVOICE_ID, PAYMENT_REF

IN, IN001, PY001

IN, IN002,PY002

IN, IN003, PY003

CA, PY001, IN001

CA, PY004,

CA, PY005,

];

NoConcatenate

Invoices:

LOAD

  *

Resident ALL

WHERE INVOICE_TYPE = 'IN';

Payments:

LOAD

  *

Resident ALL

WHERE INVOICE_TYPE = 'CA' AND LEN(PAYMENT_REF)=0;

Drop Table ALL;

Mark

[Peter_Cammaert]

Replace table label PAYMENT_TABLE: (including the colon) with the following:

CONCATENATE (INVOICE_TABLE)

:

BTW payments without invoices?

Peter