Scripting Issue

[#1]

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

[trdandamudi]

Can you be little more specific and what is the output that you are expecting...

[settu_periasamy · Accepted solution]

Try this

T1:

Load case,Entity,[American Entity] from source;

Left join (T1)

Load case, if (Wildmatch(concat (distinct [American Entity]),'*Y*'),'Y','N') As [American case]

Resident T1 Group by case;

Doubt: 4th case should be 'N', right?

[vinieme12]

How is American Case evaluated, what is the logic here? do u have another data  table that holds that info??

[#5]

Hi, so the first 3 columns are already provided (which states whether is is american or not) and I want to create the 4th column, which basically tells you if that case has atleast 1 american entity or not.

[#6]

Yes Sethu, sorry Los Angeles should have a Y, thank you for the help, I'll try that

[#7]

Hi, does American entity need to be in square brackets?

[trdandamudi]

Yes, it has to be in square brackets because it has space.

[effinty2112]

Hi Ben,

                    We can get the result we need with the appropriate expression without resorting to rewriting the script:

Case	Entity	American Entity	if(Sum(Aggr(NODISTINCT Count({$<[American Entity] = {'Y'}>}Entity),Case))>0,'Y','N')
1	London	N	Y
1	New York	Y	Y
2	Hong Kong	N	N
2	London	N	N
2	Paris	N	N
3	New York	Y	Y
3	San Francisco	Y	Y
3	Texas	Y	Y
4	Barcalona	N	Y
4	London	N	Y
4	Los Angeles	Y	Y

We can take out all dimensions except for the Case and get this:

Case	if(Sum(Aggr(NODISTINCT Count({$<[American Entity] = {'Y'}>}Entity),Case))>0,'Y','N')
1	Y
2	N
3	Y
4	Y

Regards

Andrew

[#10]

Hi, is that inside the load script or an expression in the front end?