Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis

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

TABLE 1 :

Project

Value

A2
B3
C5
D1
E2
F5
G7

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 ?

1 Solution

Accepted Solutions
kaushiknsolanki

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

7 Replies
kaushiknsolanki

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

sujeetsingh
Master III
Master III

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
Master III
Master III

Here is a sample for you

MK_QSL
MVP
MVP

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jagan
MVP
MVP

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.

dhirajkumar
Creator
Creator

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