Deleting row if value in column only appears once

ihuynhi — Thu, 12 May 2022 11:56:22 GMT

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?

Re: Deleting row if value in column only appears once

vchuprina — Sun, 15 May 2022 12:38:47 GMT

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):

After Join:

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)

Regards,

Vitalii

topic Deleting row if value in column only appears once in QlikView

Deleting row if value in column only appears once

Re: Deleting row if value in column only appears once