topic Re: Display Record if Join/Table doesn't exist in QlikView

Display Record if Join/Table doesn't exist

ciaran_mcgowan — Mon, 12 May 2014 13:23:51 GMT

Hi,

I have a customer who uses QV to display the amount owed to them by their clients. They have 3 tables that are used to calculate this; SALE -> PAYMENTMETHOD -> PAYMENT.

SALE joined to PAYMENTMETHOD using SaleID

PAYMENTMETHOD joined to PAYMENT using PaymentID

A problem arises when the payment method is set to Layaway and there is no initial payment, the debt is not displayed because there is no PaymentID. Is there a way to create a dummy record for the PAYMENT table if the PAYMENTMETHOD = 'Layaway'? As it currently stands, users have to populate the PAYMENT table with an initial value of £0.00.

Thanks

Re: Display Record if Join/Table doesn't exist

Mon, 12 May 2014 13:28:39 GMT

Ciaran,

do you have 1 table after this join, or there are 3 associated tables?

regards

Darek

Re: Display Record if Join/Table doesn't exist

ciaran_mcgowan — Mon, 12 May 2014 13:37:05 GMT

Hi Dariusz,

I'm not sure I follow the question, these three tables are not linked to each other: SALES & PAYMENT are not directly linked, they are only linked to PAYMENTMETHOD.

The report takes the amount of money the sale was for, checks the payment method and then takes away the sum(payed_amount). But if there has been nothing paid, the total amount left to be paid isn't displayed as there is no PaymentID stored in the PAYMENT table that matched the PAYMENTMETHOD table.

I hope that explains it.

Re: Display Record if Join/Table doesn't exist

Mon, 12 May 2014 13:53:44 GMT

Ciaran,

You may generate those dummy rows in load script.

After loadingsome table you may use resident and where clauses to find some rows in previously loaded table.

Than you may prepare data for rows you find and concatenate it with another table. It is rather easy. But if you need more assistance, please attach script part where those 3 tables are loaded.

You may also try to use set analysis in your expression.

regards

Darek