topic Scripting Issue in QlikView

Scripting Issue

Sat, 13 Aug 2016 16:35:33 GMT

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

Re: Scripting Issue

trdandamudi — Sat, 13 Aug 2016 18:56:44 GMT

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

Re: Scripting Issue

settu_periasamy — Sat, 13 Aug 2016 19:24:01 GMT

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?

Re: Scripting Issue

vinieme12 — Sun, 14 Aug 2016 06:51:22 GMT

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

Re: Scripting Issue

Sun, 14 Aug 2016 10:52:35 GMT

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.

Re: Scripting Issue

Sun, 14 Aug 2016 10:59:11 GMT

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

Re: Scripting Issue

Sun, 14 Aug 2016 20:36:01 GMT

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

Re: Scripting Issue

trdandamudi — Mon, 15 Aug 2016 00:46:05 GMT

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

Re: Scripting Issue

effinty2112 — Mon, 15 Aug 2016 09:46:49 GMT

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

Re: Scripting Issue

Mon, 15 Aug 2016 15:12:12 GMT

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

Re: Scripting Issue

effinty2112 — Mon, 15 Aug 2016 15:34:36 GMT

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

Re: Scripting Issue

effinty2112 — Mon, 15 Aug 2016 15:37:08 GMT

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

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

Re: Scripting Issue

Mon, 15 Aug 2016 15:37:51 GMT

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

Re: Scripting Issue

richard_chilvers — Mon, 15 Aug 2016 16:09:18 GMT

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

Re: Scripting Issue

effinty2112 — Mon, 15 Aug 2016 16:14:32 GMT

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

Re: Scripting Issue

richard_chilvers — Tue, 16 Aug 2016 07:45:47 GMT

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