Getting the values from column B where column A is only of 1 type

[Pico]

Hi all,

this is my situation, I have two columns:

Name	Object
Mark	Pen
Mark	Bottle
Mark	Book
Lisa	Book
Lisa	Pen
Paul	Pen

I would like to know how can I filter in order to obtain "The people that have ONLY a Pen" (in this case, Paul)

or "The people  that have ONLY book and pen" (Lisa)

The problem is that I have 10+ objects types and a LOT of people... and the possible combinations of filters are A LOT, so I would like to have something "structured"

How can I achieve this?

Thank you so much, have a nice day

[Aurelien_Martinez]

Hi,

You can create a field in the dataoad script.

1. you count the number of object by name

2. if the number is 1 you add a value in the new field "Filter"

data:
LOAD
  *
Inline [
Name,	Object
Mark,	Pen
Mark,	Bottle
Mark,	Book
Lisa,	Book
Lisa,	Pen
Paul,	Pen
];

join(data)
LOAD
  Name,
  Count(DISTINCT Object) as nb_object
Resident data
Group by
  Name
;

LOAD
  Name,
  Object,
  If(nb_object=1, 'The people that have ONLY a ' & Object, Null()) as Filter
Resident data
;

DROP Table data;

[Pico]

Thank you.

Sadly this works well with only the first example. 

I have 10+ object types, and I will need also to answer to "show people that have only object 1, 4, 5, 13".

[Aurelien_Martinez]

Ok, sorry I didn't read all the problem ^^'

data:
LOAD
  *
Inline [
Name,	Object
Mark,	Pen
Mark,	Bottle
Mark,	Book
Lisa,	Book
Lisa,	Pen
Paul,	Pen
];

join(data)
LOAD
  Name,
  Count(DISTINCT Object) as nb_object,
  Concat(Object, ' and ') as Objects
Resident data
Group by
  Name
Order By
  Object
;

LOAD
  Name,
  Object,
  'The people that have ONLY a ' & Objects as Filter
Resident data
;

DROP Table data;