Remove all instances of one column value based on another column's value

[#1]

Hello all,

For a dashboard I am creating, we have a table of all, non-distinct records ID's in one column, and I am trying to remove all instances of this record ID if it has a value of "SPAM" for "topic" in a separate column.

For example, for REC_ID 123 below, since one instance of this REC_ID has the TOPIC = SPAM, I want to remove all rows with this REC_ID, and be left with the table underneath it.

REC_ID	TOPIC
1234	HEALTH
1234	GENERAL COMMENT
1234	SPAM
3456	HEALTH
6788	INFORMATION

REC_ID	TOPIC
3456	HEALTH
6788	INFORMATION

Thank you.

[pipuindia99]

you can write the below expression for topic column.

pick(not match(topic,'spam')),topic

and add rec_id as it is

[sunny_talwar]

May be something like this:

Table:

LOAD * INLINE [

    REC_ID, TOPIC

    1234, HEALTH

    1234, GENERAL COMMENT

    1234, SPAM

    3456, HEALTH

    6788, INFORMATION

];

Check:

LOAD DISTINCT REC_ID as Check

Resident Table

Where TOPIC = 'SPAM';

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where not Exists(Check, REC_ID);

DROP Tables Table, Check;

[Anil_Babu_Samineni]

Doe this you need Front - End ?

[#5]

Yes, I am trying to do this in a table on a dashboard

[sunny_talwar]

On front end, you can try to add set analysis to your expression like this:

Sum({<REC_ID = e({<TOPIC = {'SPAM'}>} REC_ID)>}Measure)

I am assuming your expression is Sum(Measure)