Check itens included in other table

[Anonymous]

I have two tables called BILL and PAYMENT.

How can i create in BILL table a column to indicate only the bills that has a payment in PAYMENT table associated?

[petter]

In your load script you could do this:

PAYMENT:

LOAD

     BillNo,

     .....

FROM      // or ;SQL SELECT ...

     .....;

HAS_PAYMENTS:

LOAD DISTINCT

     PaymentBillNo

RESIDENT

     PAYMENT;

BILL:

LOAD

     BillNo,

     If( Exists('PaymentBillNo') , 'Y' , 'N') AS HasPayments,

     ....

FROM        // or ;SQL SELECT ....

     .....;

DROP TABLE HAS_PAYMENTS;

[Peter_Cammaert · Accepted solution]

Same with mapping table. Usually much faster...

PAYMENT:

LOAD BillID, ..... // Load everything you need

FROM DataSource (options);

MapHasPayment:

MAPPING LOAD DISTINCT BillID, 'Y' AS F2 RESIDENT PAYMENT;

BILL:

LOAD BillID,

     applymap('MapHasPayment', BillID, 'N') AS HasPaymentFlag,

     .... // Load everything you need

FROM OtherDataSource (options);

No need to drop Mapping Table. Disappears automatically at the end of the script run