topic Remove rows- Duplicate OrderID, Unique ProductID in QlikView

Remove rows- Duplicate OrderID, Unique ProductID

ellensadler — Wed, 25 Nov 2020 16:16:04 GMT

I am working with an excel file that contains OrderID's and ProductID's, among various other fields. I need to delete ALL ORDERS that contain one of three products (we'll call them Products AA, BB, and CC for now). The issue here is that there are multiple rows for each OrderID, and the only thing making each row unique is the ProductID it lists. Below is a very simplified version of what I mean:

This is as far as I've gotten so far- before loading the file into Qlik Sense, I added a column in excel called "Exclusions" where each row shows 0 or 1 depending on whether or not the ProductID for that row is AA, BB, or CC. Once the file was uploaded via the Data Load Editor, I loaded a new table that aggregated all the "Exclusion" values as grouped by OrderID. This successfully assigned each OrderID an OrderSum of 0 if it needed to be kept in the data. Beyond this, I have no clue what to do. I've tried just about every Join, Keep, Select, Mapping, or Load statement I could possibly find. Nothing has worked in any form, however simple or complicated the proposed solution. Pasted below is what HAS worked, i.e. everything up to the aggregated scores:

LOAD
    OrderID,
    OrderDate,
    ProductID,
    Product,
    Quantity,
    Cost,
    Sales,
    marginP,
    margin2,
    EmployeeID,
    CustomerID,
    Exclusion
FROM [lib://Data/Sales.xls]
(biff, embedded labels, table is Orders$);

SumTable$:
LOAD OrderID, Sum(Exclusion) as OrderSum Resident Orders$ Group By OrderID;

Re: Remove rows- Duplicate OrderID, Unique ProductID

maxgro — Sat, 23 Feb 2019 20:29:42 GMT

Try this

// test table, replace with your excel
t:
load * Inline [
OrderID, ProductID
1,AA
1,FF
1,CC
2,BB
2,FF
3,DD
3,EE
];

// orders to exclude

t1:
load OrderID as ExcludedOrderID
resident t
where match(ProductID, 'AA','BB','CC');

// load orders

f:
noconcatenate load *
resident t
where not exists(ExcludedOrderID, OrderID) ;

drop table t;
drop table t1;

Re: Remove rows- Duplicate OrderID, Unique ProductID

ellensadler — Sat, 23 Feb 2019 23:28:34 GMT

Thanks for your prompt response! Unfortunately the actual data set that I'm working with isn't just 7 rows and 2 fields- it's 12 fields and almost 2200 rows. What will that mean for the Inline statement? (And sorry if this question seems too elementary! I tried searching the Qlik help articles for more on Inline statements but had little luck.)

Re: Remove rows- Duplicate OrderID, Unique ProductID

pradosh_thakur — Sun, 24 Feb 2019 08:22:59 GMT

@maxgro used inline load just to demonstrate. Please usehttps://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/LoadData/use-data-load-editor.htm to learn more on how to load data automatically

Re: Remove rows- Duplicate OrderID, Unique ProductID

maxgro — Sun, 24 Feb 2019 09:41:58 GMT

I have attached two files, an excel with some test data; I just used few rows and 5 columns; you can adapt the code to your excel

and a small Sense app with the script

t:
LOAD
    OrderID,
    ProductID,
    Col3,
    Col4,
    Col5
FROM [lib://TestFolder/as excel.xlsx]
(ooxml, embedded labels, table is Foglio1);

t1:
load OrderID as ExcludedOrderID
resident t
where match(ProductID, 'AA','BB','CC');

// load orders
f:
noconcatenate load *
resident t
where not exists(ExcludedOrderID, OrderID) ;

drop table t;
drop table t1;

you can see the result in the table in Sense app