Include the Null rows from from one table not matching another table

Tue, 20 Nov 2012 17:34:33 GMT

Hi All

Here go's with my first post I'm a Tsql and Crystal reports developer by trade and now dipping my finger into the world of Qlikview.

I have two tables 1st is goods receved 2nd is goods invoiced what I want to do is return all rows from the goods receved and any matched rows from goods invoiced if there is no goods invoiced I need to show the goods receved data I have tried to use left join a good friend has done some scripting for me and I have changed it slightly to see if I can get the missing data but didn't find the answer yet could any one point out where I'm going wrong please?

Many thanks in advance for the answer

Kind regards

Simon

Temp_Matched_GRNs:

Load

GRN_Line,

sum(PRECEIPTD_QTYUOM_0) as Received_Qty

FROM

$(vLiveLoadPath)PRECEIPTD.qvd

(qvd)

where PRECEIPTD_CPR_0 <>0

Group by GRN_Line;

left join

Load

GRN_Line,

sum(PINVOICED_QTYUOM_0) as Invoiced_Qty

FROM

$(vLiveLoadPath)PINVOICED.qvd

(qvd)

//where PINVOICED_TYPORI_0=2 //zero value and non stock invoices not relevant;

where GRN_Line<> Null()or PINVOICED_AMTNOTLIN_0 <>0 or PINVOICED_TYPORI_0=2 //zero value and non stock invoices not relevant;

Group by GRN_Line;

Matched_GRNs:

Load

GRN_Line,

Invoiced_Qty,

Received_Qty,

if(Invoiced_Qty = 0,'Zero Value Invoice',if(Invoiced_Qty = Null(),'Not Invoiced',if(Invoiced_Qty <> 0 and Invoiced_Qty < Received_Qty,'Part Matched','Fully Matched'))) as GRN_Matched //allow for multiple invoices for a GRN

//if(Invoiced_Qty = 0,'No Invoice',if(Invoiced_Qty <> 0 and Invoiced_Qty < Received_Qty,'Part Matched','Fully Matched')) as GRN_Matched //allow for multiple invoices for a GRN

Resident Temp_Matched_GRNs

where Received_Qty > 0;

drop table Temp_Matched_GRNs;

Re: Include the Null rows from from one table not matching another table

fosuzuki — Thu, 22 Nov 2012 17:18:10 GMT

Can you explain what's wrong with your code? What is the actual result and what you expected it to do?

Try changing

if(Invoiced_Qty = null(), .....

if(isnull(Invoiced_Qty), .....

topic Include the Null rows from from one table not matching another table in QlikView

Include the Null rows from from one table not matching another table

Re: Include the Null rows from from one table not matching another table