topic Re: Set analysis in QlikView

Set analysis

Fri, 20 Nov 2020 18:02:30 GMT

Hi everyone, I have small issue with set analysis. I have two tables with some fileds as presented below :

TABLE 1 :

Project	Value
A	2
B	3
C	5
D	1
E	2
F	5
G	7
H	9

TABLE 2:

Project_to_filter
B
D
F
H

I need to calculate few different expression but in every one of them i want to use set analysis to filter project from table1 which are included in table2.

So for example Sum({<XXX>} Value) should return (3+1+5+9) = 18

Any idea ?

Re: Set analysis

kaushiknsolanki — Thu, 07 Aug 2014 07:36:31 GMT

HI,

If they are linked then you can create a flag which can get you the output. same like below.

Load Project, Value From ABC;

Load Project_to_Filter as Project,'1' as Filter_Flag From PQR;

Now in chart, put dimension as Project and enter expression as

Sum({<Filter_Flag = {"1"}>}Value)

Regards,

Kaushik Solanki

Re: Set analysis

sujeetsingh — Thu, 07 Aug 2014 07:39:09 GMT

Use this script

Table1:

LOAD * INLINE [

Proj, Val

A, 2

B, 3

C, 5

D, 2

E, 2

F, 5

G, 7

H, 9

];

Table2:

LOAD * INLINE [

Project_to_Filter

];

Join(Table1)

LOAD

Project_to_Filter as Proj

,Project_to_Filter

Resident Table2;

DROP Table Table2;

Data: NoConcatenate

LOAD*

,if(Project_to_Filter=Proj,'Yes','No') as Include_Flag

Resident Table1;

DROP Table Table1;

Re: Set analysis

sujeetsingh — Thu, 07 Aug 2014 07:41:05 GMT

Here is a sample for you

Re: Set analysis

MK_QSL — Thu, 07 Aug 2014 07:45:17 GMT

USE BELOW SCRIPT

=================================================

T1:

Load * Inline

[

Project,Value

A, 2

B, 3

C, 5

D, 1

E, 2

F, 5

G, 7

H, 9

];

T2:

Load

Project_to_filter as Project,

1 as Flag

Inline

[

Project_to_filter

];

=========================

Below expression will give you desired result...

=SUM({<Flag = {1}>}Value)

Re: Set analysis

jonathandienst — Thu, 07 Aug 2014 07:46:26 GMT

Build a mapping table with the second table and then applymap a flag into the main load:

MapExclusions:

Mapping LOAD

Project_to_filter,

1 As Exclude

Inline

[

Project_to_filter

];

TABLE1:

LOAD Project,

Value,

ApplyMap('MapExclusions', Project, 0) As ExcludeFlag

Inline

[

Project, Value

A,2

B,3

C,5

D,1

E,2

F,5

G,7

H,9

];

Then use the flag in a chart expression:

Sum({<ExcludeFlag = {0}>} Value)

See attached.

HTH

Jonathan

Edit: this excludes the filter projects. Its easy to switch around

Re: Set analysis

jagan — Thu, 07 Aug 2014 07:51:06 GMT

HI,

Try this script

Table1:

LOAD

INLINE [

Proj, Val

A, 2

B, 3

C, 5

D, 2

E, 2

F, 5

G, 7

H, 9

];

LEFT JOIN (Table1)

LOAD

Project_to_Filter AS Proj

1 AS Flag

INLINE [

Project_to_Filter

];

Set analysis expression

=SUM({<Flag = {1}>}Val)

Hope this helps you.

Regards,

jagan.

Re: Set analysis

Anonymous — Thu, 07 Aug 2014 08:06:02 GMT

Hi Jackub,

You can try like that for example-

Test:

LOAD * INLINE [

Project , Value

A, 2

B, 3

C, 5

D, 1

E, 2

F, 5

G, 7

H, 9

];

Test1:

LOAD * INLINE [

Project_to_Filter

];

Test2:

load Project_to_Filter as Project

Resident Test1;

drop Table Test1;

then use set analysis as below

sum({$<Project = {B,D,F,H}>}Value)

I think that is helpful for you..