Set analysis

[#1]

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 ?

[kaushiknsolanki · Accepted solution]

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

[sujeetsingh]

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

    B

    D

    F

    H

];

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;

[sujeetsingh]

Here is a sample for you

[MK_QSL]

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

  B

  D

  F

  H

];

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

Below expression will give you desired result...

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

[jonathandienst]

Hi

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

  B

  D

  F

  H

];

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

[jagan]

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

    B

    D

    F

    H

];

Set analysis expression

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

Hope this helps you.

Regards,

jagan.

[Anonymous]

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

    B

    D

    F

    H

];

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