Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Scripting Issue

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?

CaseEntityAmerican EntityAmerican Case
1LondonNY
1New YorkYY
2ParisNN
2LondonNN
2Hong KongNN
3San FranciscoYY
3TexasYY
3New YorkYY
4BarcalonaNY
4LondonNY
4Los AngelesYY
15 Replies
effinty2112
Master
Master

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

effinty2112
Master
Master

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

Not applicable
Author

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).

richard_chilvers
Specialist
Specialist

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

effinty2112
Master
Master

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

richard_chilvers
Specialist
Specialist

This is similar to my idea, and is worthwhile if the number of American cities is required for each 'case'.