Display Only Rows That Satisfies ALL Conditions Applied In Expressions

Announcements Write Table now available in Qlik Cloud Analytics: Read Blog

• Qlik Community
• :
• All Forums
• :
• QlikView App Dev
• :
• Display only rows that satisfies ALL conditions ap...

Options

• Subscribe to RSS Feed
• Mark Topic as New
• Mark Topic as Read
• Float this Topic for Current User
• Bookmark
• Subscribe
• Mute
• Printer Friendly Page

All communityThis boardKnowledge baseUsersProducts cancel Turn on suggestions Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Showing results for  Search instead for  Did you mean:  Not applicable ‎2014-09-25 12:58 PM

• Mark as New
• Bookmark
• Subscribe
• Mute
• Subscribe to RSS Feed
• Permalink
• Print
• Report Inappropriate Content

Display only rows that satisfies ALL conditions applied in Expressions

I have attached the sample file and output screenshot. I want to display only those rows that satisfies both the conditions (AND) instead of OR. i.e as per the expression applied, only one row satisfying both the condition should be displayed. Is this possible ?

Also, this is just a sample but in real scenario, I have about 10 such numeric columns that has lower and upper limits coming through variables whose values are entered by users

Data:

Sample_Data:

Load * Inline [

Company, ID, AccountType, No_Of_Emp, Revenue

A, 1, AAA, 28,300000

B, 1, BBB, 70,500000

C, 1, CCC, 80,450000

D,2, AAA, 200,2000000

E,2, BBB, 100,5000000

F,2, CCC, 70,750000

G,3, AAA, 300,6000000

H,4, BBB, 400,4500000

I,5, AAA, 100,2000000

J,6, CCC, 200,1500000

K,7, CCC, 250,2000000

L,8, AAA, 300,3000000

M,9, BBB, 100,400000

N,10,BBB, 105,900000

];

Dimension:

1. Company

2. ID

Expressions:

1. = Sum(Aggr(If(sum(No_Of_Emp)>200 AND SUM(No_Of_Emp) < 500, Sum(No_Of_Emp)), Company,ID))

2. = Sum(Aggr(If(sum(Revenue)>200000 and sum(Revenue) < 3000000, Sum(Revenue)), Company,ID))

• Tags:
• qlikview_scripting

TEST.qvw 6 KB Ditto - same here! 1,095 Views 0 Likes Reply

• All forum topics
• Previous Topic
• Next Topic

5 Replies anbu1984 Master III ‎2014-09-25 01:59 PM

• Mark as New
• Bookmark
• Subscribe
• Mute
• Subscribe to RSS Feed
• Permalink
• Print
• Report Inappropriate Content

Yes. Check this qvw

limit_rows_an.qvw 988 Views 1 Like Reply anbu1984 Master III ‎2014-09-25 02:11 PM

• Mark as New
• Bookmark
• Subscribe
• Mute
• Subscribe to RSS Feed
• Permalink
• Print
• Report Inappropriate Content

In response to anbu1984

Check this

limit_rows_an_1.qvw 989 Views 1 Like Reply jyothish8807 Master II ‎2014-09-25 02:12 PM

• Mark as New
• Bookmark
• Subscribe
• Mute
• Subscribe to RSS Feed
• Permalink
• Print
• Report Inappropriate Content

Hi Sheetal,

Try this:

Expressions:

1. = Sum(Aggr(If((No_Of_Emp)>200 AND (No_Of_Emp) < 500, Sum(No_Of_Emp)), Company,ID))

2. = Sum(Aggr(If((Revenue)>200000 and (Revenue) < 3000000, Sum(Revenue)), Company,ID))

Regards

KC

Best Regards,KC 989 Views 0 Likes Reply jyothish8807 Master II ‎2014-09-25 02:16 PM

• Mark as New
• Bookmark
• Subscribe
• Mute
• Subscribe to RSS Feed
• Permalink
• Print
• Report Inappropriate Content

In response to jyothish8807

I guess you can remove SUM in the beginning also.Try both.

1. = Aggr(If((No_Of_Emp)>200 AND (No_Of_Emp) < 500, Sum(No_Of_Emp)), Company,ID)

2. = Aggr(If((Revenue)>200000 and (Revenue) < 3000000, Sum(Revenue)), Company,ID)

Regards

KC

Best Regards,KC 989 Views 0 Likes Reply Not applicable ‎2014-09-25 04:22 PM Author

• Mark as New
• Bookmark
• Subscribe
• Mute
• Subscribe to RSS Feed
• Permalink
• Print
• Report Inappropriate Content

In response to anbu1984

Thanks Anbu. These were very helpful and works with the above sample data and expressions. However, if I apply these to my another sample that has optional upper and lower limits applied to both columns and the limits controlled by variables whose values are entered by user input box, it suppresses all rows with 0 values in any of the column even when the limits are not selected which should not be the case.

I have attached the sample here. The optional lower and upper limits are working for both columns. If I apply your solution, the AND condition works but it suppresses all rows with 0 values in any of the column even when the limits are selected or not selected

If you cannot open the file, the data, dimension and expressions used in this sample are (vEmpLower, vEmpUpper , vRevLower and vRevUpper are variables whose values are entered through input box)

Data:

Sample_Data:

Load * Inline [

Company, ID, AccountType, No_Of_Emp, Revenue

A, 1, AAA, 28,300000

B, 1, BBB, 70,500000

C, 1, CCC, 80,450000

D,2, AAA, 200,2000000

E,2, BBB, 100,5000000

F,11, CCC, 70,750000

F,3, DDD, 0,750000

G,3, AAA, 300,6000000

G,12, BBB, 300,0

H,4, BBB, 400,4500000

I,5, AAA, 100,2000000

J,6, CCC, 200,1500000

K,7, CCC, 250,2000000

K,14, CCC, 0,2000000

L,8, AAA, 300,3000000

M,9, BBB, 100,400000

N,10,BBB, 105,900000

];

Dimension:

1. Company

2. ID

Expressions: - Working for optional limits but not AND condition

1.

if(vEmpLower <> '' AND vEmpUpper <> '', Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower) and sum(No_Of_Emp) < (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower <> '' AND vEmpUpper = '',Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper <> '',Sum(Aggr(If(sum(No_Of_Emp)< (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper = '',sum(No_Of_Emp)))))

2.

if(vRevLower <> '' AND vRevUpper <> '', Sum(Aggr(If( sum(Revenue) >(vRevLower) and sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower <> '' AND vRevUpper = '',Sum(Aggr(If( sum(Revenue) >(vRevLower), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper <> '',Sum(Aggr(If(sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper = '',sum(Revenue)))))

Expressions modified to: - Working for AND conditions and optional limits but it suppresses rows with 0 values when any limits are not selected

1.

IF(if(vRevLower <> '' AND vRevUpper <> '', Sum(Aggr(If( sum(Revenue) >(vRevLower) and sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower <> '' AND vRevUpper = '',Sum(Aggr(If( sum(Revenue) >(vRevLower), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper <> '',Sum(Aggr(If(sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper = '',sum(Revenue))))),if(vEmpLower <> '' AND vEmpUpper <> '', Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower) and sum(No_Of_Emp) < (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower <> '' AND vEmpUpper = '',Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper <> '',Sum(Aggr(If(sum(No_Of_Emp)< (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper = '',sum(No_Of_Emp))))))

2.

IF(sum_No_Of_Emp,if(vRevLower <> '' AND vRevUpper <> '', Sum(Aggr(If( sum(Revenue) >(vRevLower) and sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower <> '' AND vRevUpper = '',Sum(Aggr(If( sum(Revenue) >(vRevLower), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper <> '',Sum(Aggr(If(sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper = '',sum(Revenue))))))

test_2.qvw 989 Views 0 Likes Reply Post Reply Tags

• new_to_qlikview
• new to qlikview
• qlikview_scripting
• qlikview_layout_visuali…
• qlikview_creating_analy…
• qlikview
• script
• chart
• expression
• date
• load
• table
• set_analysis
• expressions
• macro
• analysis
• scripting
• pivot
• set
• dimension

View All ≫ Community Browser

• All Forums
  • GeoAnalytics
  • QlikView Administration
  • QlikView App Dev
  • QlikView Connectivity
  • QlikView Integrations
  • Qlik NPrinting