Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
1 Solution

Accepted Solutions

Re: Scripting Issue

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?

15 Replies
trdandamudi
Honored Contributor

Re: Scripting Issue

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

Re: Scripting Issue

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
Esteemed Contributor II

Re: Scripting Issue

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

Not applicable

Re: Scripting Issue

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.

Not applicable

Re: Scripting Issue

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

Highlighted
Not applicable

Re: Scripting Issue

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

trdandamudi
Honored Contributor

Re: Scripting Issue

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

effinty2112
Honored Contributor

Re: Scripting Issue

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')
1LondonNY
1New YorkYY
2Hong KongNN
2LondonNN
2ParisNN
3New YorkYY
3San FranciscoYY
3TexasYY
4BarcalonaNY
4LondonNY
4Los AngelesY

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')
1Y
2N
3Y
4Y

Regards

Andrew

Not applicable

Re: Scripting Issue

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