Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ihuynhi
Contributor II
Contributor II

Deleting row if value in column only appears once

Hi!

I'm new to Qlik so apologies in advance if I explain things unclearly. 

I have a table and I want to remove a whole row based on if the value of a specific column appears only once. I know that I can "drop" rows (or rather not select them) based on column value with

Where [fieldname] <> '0' (selects only values where the column value is not zero)

but I don't know how to do this with column value count. Is there a way to do this?

I also know from another post that I can count if a value appears only once in a column with

Count(DISTINCT{<Value = {"=Count(Value) = 1"}>}Value)

Is this maybe useful?

Labels (2)
1 Solution

Accepted Solutions
vchuprina
Specialist
Specialist

Hi,

You can remove lines that appear only once, in the script part or in your UI objects.

Script:

First, you should count a number of values in this column in a separate table, then leave values that meet your condition (NumOfID > 1)  and join (inner join) them to the main table, see the example below

//Source Table

DATA_TMP:
LOAD * Inline [
BusinessEntityID, OrderDate, earningsPerSale, Model
1, 05-12-2022, 50, A
2, 05-12-2021, 60, A
2, 05-13-2021, 70, B
2, 05-14-2021, 90, C
3, 05-12-2023, 15, C
3, 05-13-2023, 550, D
5, 05-14-2020, 150, E
];

Inner Join(DATA_TMP) // Join to data source to leave BusinessEntityID that appears more than one time
LOAD
     BusinessEntityID
Where NumOfID > 1; //Load BusinessEntityID that appears more than 1 time in table
LOAD
     BusinessEntityID,
     Count(BusinessEntityID) AS NumOfID //Count number of BusinessEntityID
Resident DATA_TMP
Group By BusinessEntityID;

Before Join (ID 1 and ID 5 appear only once in the table):

vchuprina_0-1652617458929.png

After Join:

vchuprina_1-1652617516006.png

UI solution (Set Analysis)

If you don't want to remove IDs that appear only once in your table from the application you can filter them on UI with Set Analysis.

Instead of Sum(earningsPerSale) I use 

SUM({<BusinessEntityID = {"=Count(BusinessEntityID)>1"}>}earningsPerSale)

vchuprina_2-1652617892221.png

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").

View solution in original post

1 Reply
vchuprina
Specialist
Specialist

Hi,

You can remove lines that appear only once, in the script part or in your UI objects.

Script:

First, you should count a number of values in this column in a separate table, then leave values that meet your condition (NumOfID > 1)  and join (inner join) them to the main table, see the example below

//Source Table

DATA_TMP:
LOAD * Inline [
BusinessEntityID, OrderDate, earningsPerSale, Model
1, 05-12-2022, 50, A
2, 05-12-2021, 60, A
2, 05-13-2021, 70, B
2, 05-14-2021, 90, C
3, 05-12-2023, 15, C
3, 05-13-2023, 550, D
5, 05-14-2020, 150, E
];

Inner Join(DATA_TMP) // Join to data source to leave BusinessEntityID that appears more than one time
LOAD
     BusinessEntityID
Where NumOfID > 1; //Load BusinessEntityID that appears more than 1 time in table
LOAD
     BusinessEntityID,
     Count(BusinessEntityID) AS NumOfID //Count number of BusinessEntityID
Resident DATA_TMP
Group By BusinessEntityID;

Before Join (ID 1 and ID 5 appear only once in the table):

vchuprina_0-1652617458929.png

After Join:

vchuprina_1-1652617516006.png

UI solution (Set Analysis)

If you don't want to remove IDs that appear only once in your table from the application you can filter them on UI with Set Analysis.

Instead of Sum(earningsPerSale) I use 

SUM({<BusinessEntityID = {"=Count(BusinessEntityID)>1"}>}earningsPerSale)

vchuprina_2-1652617892221.png

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").