How to remove records when ID has at least one related measure = 0

Question

Essentially, with reference to the table, I want to exclude all matching IDs in this case C123 because it has at least one of the UsedResources = 0.

Any help or advice would be very appreciated here.

PersonalID	ID_Holder	AssigmentTags	UsedResources
C123	Kratos	AS001	0
C123	Kratos	AS999	15
C123	Kratos	AS542	20
P567	Zesus	AS874	25
P567	Zesus	AS123	10
P567	Zesus	AS983	5

I'd very much like solutions both in the script and at the front end. If not too much to ask? — LoKi_Asterix, Oct 21 '22 at 12:05

score 0 · Accepted Answer · answered Oct 24 '22 at 23:37

Script

You can flag these records and the use the flag to filter them out in the UI

RawData:
Load * Inline [
PersonalID, ID_Holder, AssigmentTags, UsedResources
C123      , Kratos   , AS001        , 0
C123      , Kratos   , AS999        , 15
C123      , Kratos   , AS542        , 20
P567      , Zesus    , AS874        , 25
P567      , Zesus    , AS123        , 10
P567      , Zesus    , AS983        , 5
];

join

Load
  PersonalID,
  if(MinUsedResources > 0, 1, 0) as HasNonZeroResources
;  
Load
distinct
  PersonalID,
  min(UsedResources) as MinUsedResources
Resident
  RawData
Group By
    PersonalID
;

Once the app is reloaded then HasNonZeroResources field can be used in the expressions:

With Set analysis:

count( {< HasNonZeroResources = {1} >} AssigmentTags)

Without Set analysis:

// not sure how effective is this
count( AssigmentTags ) * HasNonZeroResources

Expression

One way is to only include PersonalID for which the minimum value for UsedResources is

count( {< PersonalID = {"=min(UsedResources) > 0"} >}  AssigmentTags)

How to remove records when ID has at least one related measure = 0

1 Answers1