Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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;

1 Reply
fosuzuki
Not applicable

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

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(), .....

to

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