Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
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".
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 - Champion III
Partner - Champion III

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
Luminary Alumni
Luminary Alumni

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