Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ellensadler
Contributor
Contributor

Remove rows- Duplicate OrderID, Unique ProductID

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:

Screen Shot 2019-02-23 at 2.02.03 PM.pngThis 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;

4 Replies
maxgro
MVP
MVP

 

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;

ellensadler
Contributor
Contributor
Author

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.)
pradosh_thakur
Master II
Master II

hi

@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

Learning never stops.
maxgro
MVP
MVP

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

Data in Excel.png

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

Table in Sense.png