1 Reply Latest reply: Nov 22, 2012 12:18 PM by Fernando Obara Suzuki RSS

    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

      //[D:\Dashboard Transfer\XKODashboards\Load\data\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

      //[D:\Dashboard Transfer\XKODashboards\Load\data\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;