topic Re: Check itens included in other table in QlikView

Check itens included in other table

Anonymous — Wed, 30 Dec 2015 14:53:49 GMT

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 — Wed, 30 Dec 2015 15:21:43 GMT

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 — Wed, 30 Dec 2015 15:29:32 GMT

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