DISTINCT in qlikview script

andradinu — Sat, 27 Jan 2024 11:15:16 GMT

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 ;

Re: DISTINCT in qlikview script

rubenmarin — Sat, 27 Jan 2024 12:21:36 GMT

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.

Re: DISTINCT in qlikview script

andradinu — Sat, 27 Jan 2024 13:48:49 GMT

🙏 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!

topic Re: DISTINCT in qlikview script in QlikView

DISTINCT in qlikview script

Re: DISTINCT in qlikview script

Re: DISTINCT in qlikview script