Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am wondering if there is a way to take in the first 3 columns in the below tables, and get an output of the 4th.
Basically each case has a list of related entities, that produce multiple rows in the table. If one of the entities for a case
is an american entity, then I want to have a flag in each row of that case saying it is an american case.
Any ideas of how this can be done?
Maybe some kind of for loop?
Case | Entity | American Entity | American Case | |
1 | London | N | Y | |
1 | New York | Y | Y | |
2 | Paris | N | N | |
2 | London | N | N | |
2 | Hong Kong | N | N | |
3 | San Francisco | Y | Y | |
3 | Texas | Y | Y | |
3 | New York | Y | Y | |
4 | Barcalona | N | Y | |
4 | London | N | Y | |
4 | Los Angeles | Y | Y |
Hi Ben,
It's an expression in a straight table. In fact in the second table you can simplify the expression a little and take out the NODISTINCT clause when you have the single dimension of Case.
Cheers
Andrew
Hi Ben,
It a bit awkward to copy and paste the expression from the table so here it is:
if(Sum(Aggr(NODISTINCT Count({$<[American Entity] = {'Y'}>}Entity),Case))>0,'Y','N')
or
if(Sum(Aggr(Count({$<[American Entity] = {'Y'}>}Entity),Case))>0,'Y','N')
for the single dimension table where the NODISTINCT clause is not necessary.
cheers
Andrew
Hi Andrew,
Thanks, is there a way to do a count of this?
I.e. count all of the cases that are American (have a Y).
Hi
I had a similar challenge and I think I would LOAD MaxString([American Entity] AS [American Case] GROUP BY Case. This should give the field as value of Y if any American city is involved.
Might need a bit more thought, but its just an idea.
Regards
Hi Ben,
Try:
=Count(DISTINCT aggr(sum(if([American Entity] = 'Y',1)),Case)) <= Wrong!!!
=Count(Aggr( if(aggr(sum(if([American Entity] = 'Y',1)),Case)>0,1),Case))
Cheers
Andrew
This is similar to my idea, and is worthwhile if the number of American cities is required for each 'case'.