Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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".
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;