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;
