DISTINCT in qlikview script

[andradinu]

Hello,
Is it possible to add something to the script to have only DISTINCT lines on an order number? I didn't succeed and I would need it, if someone knows how. As an example, I took the order PO0000992, which has line 2 twice. I attached the model, the script and a printscreen if it helps. Thank you very much in advance

script below:

Join1: 

SQL SELECT 

poo.OrderNbr , 

poo.OrderType as OrderType_POOrder ,

poo.OrderQty as OrderQty_POOrder ,

poo.OrderTotal ,

poo.UnbilledOrderQty ,

pol.CuryLineAmt ,

pol.OrderQty ,

pol.BilledAmt ,

pol.InventoryID ,

pol.BaseOrderQty ,

pol.OpenQty ,

pol.PONbr ,

pol.ReceivedQty ,

pol.CompletedQty ,

pol.POLineNbr ,

pol.UnitWeight ,

pol.OrderType as OrderType_POLine ,

pol.CuryUnitCost ,

pol.BilledQty ,

pol.BaseUnbilledQty ,

pol.BaseBilledQty ,

pol.UnitCost ,

pol.LineNbr ,  //this must be DISTINCT

pol.OrderNbr as OrderNbr_POLine ,

pol.LineAmt ,

pol.OrderDate ,

porl.PONbr as OrderNbr_POReceiptLine, 

porl.BaseMultReceiptQty ,

porl.BaseOrigQty ,

porl.BaseReceiptQty ,

porl.InventoryID as  InventoryID_PORL ,

porl.LineNbr as LineNbr_PORL ,

porl.LocationID ,

porl.OrigLineNbr ,

porl.OrigReceiptNbr ,

porl.POLineNbr as POLineNbr_PORL,

porl.ReceiptDate ,

porl.ReceiptNbr , 

porl.ReceiptQty ,

porl.ReceiptType ,

porl.SOOrderLineNbr ,

porl.SOOrderNbr ,

porl.SOShipmentNbr ,

por.ReceiptNbr as ReceiptNbr_POReceipt , 

por.InvoiceDate ,

por.InvoiceNbr ,

item.InventoryCD as SKU,

item.BaseUnit as UM_BZ,

item.ItemType as TypeSKU ,

 apt.POLineNbr as POLineNbr_APTran,

 apt.PONbr as PONbr_APTran,

 apt.LineAmt as LineAmt_APTran,

 apt.RefNbr as INVOICE,

 apt.TranDate

FROM dbo.POOrder as poo 

INNER JOIN dbo.POLine AS pol WITH (NOLOCK) ON pol.OrderNbr =poo.OrderNbr AND poo.OrderType = pol.OrderType

LEFT JOIN dbo.POReceiptLine AS porl WITH (NOLOCK) ON pol.OrderNbr = porl.PONbr AND pol.OrderType = porl.POType AND pol.LineNbr = porl.POLineNbr 

LEFT JOIN dbo.POReceipt AS por WITH (NOLOCK) ON  por.ReceiptNbr = porl.ReceiptNbr AND  por.ReceiptType = porl.ReceiptType 

LEFT JOIN dbo.inventoryitem item WITH (NOLOCK) ON  item.InventoryID=pol.InventoryID

INNER JOIN dbo.INItemClass itemcl WITH (NOLOCK) ON item.CompanyID=itemcl.CompanyID and item.ItemClassID=itemcl.ItemClassID

LEFT JOIN .dbo.APTran apt WITH (NOLOCK) ON  /* apt.PONbr= pol.OrderNbr*/ apt.ReceiptNbr=por.ReceiptNbr and apt.LineNbr = porl.POLineNbr   //AND   apt.POLineNbr= pol.LineNbr 

where item.ItemType<>'N' 

AND

(left(upper(item.InventoryCD) ,1)='M' OR left(upper(item.InventoryCD) ,1)='P' or left(upper(item.InventoryCD) ,1)='Q'  ) 

; 

APInvoice:

SQL SELECT 

InvoiceNbr as INVOICE_APInvoice,

RefNbr as INVOICE

FROM dbo.APInvoice ;

[rubenmarin · Accepted solution]

Hi, it shows 2 lines because the query retuns two lines, one with TranDate 2/10/23 and another with null. Also both rows have different values in at least LineNbr_PORL and ReceiptDate.

You can add before "Join1: ":

LOAD * Where not Exists(LineNbr);

But only the first row of LineNbr will be loaded, wich maybe is not the one you want.

If you are not familiar with Qlik scripting maybe it will be better to solve it in the query.

Or analyze why this happens (probably a line served in 2 different transports) and change the presentation to show this.

Also check if the Order Qty is correct, you can tell if the value of each line 2 is duplicated of each one has to add his own qty.

[andradinu]

🙏 Thank you very much, Rubenmarin! Now I understand why it can't be done. I will try to remove what is not needed from the interface. Many thanks once again!