Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pnn44794
Partner - Specialist
Partner - Specialist

Hiding Rows Based On Blank Fields

Hello:

I suspect I'm making this more difficult than it needs to be, but I'm stuck at the moment.  I'm using a straight table with 23 columns.  I want to suppress / hide rows where estimate_is_active is blank or otherwise not equal to True and where project_is_active is blank or otherwise not equal to True.  So, two conditions and it might be easier to understand my ask this way:

if estimate_is_active = 'True' AND project_is_active = 'True' Then Display the Row, Else Do NOT display the Row

I do have several thousand rows with True for both estimate_is_active and project_is_active as well as rows where both are blank.

As always, thanks in advance for any and all help.

12 Replies
devarasu07
Master II
Master II

Hi,

Try like below (in your dimension key field try to use like below expression)

=if(aggr((Len(estimate_is_active)>0 or estimate_is_active = 'TRUE') and project_is_active = 'TRUE', ID),ID)

or in your expression using set analysis

Sum({<estimate_is_active={'=(Len(estimate_is_active)>0)'},project_is_active={'TRUE'},estimate_is_active={'TRUE'}>}SoldQty)

Refer to the attached sample.

Thanks,

Deva

pnn44794
Partner - Specialist
Partner - Specialist
Author

Hello Deva:

Thank you for the response.  I tried your suggestion as an Expression and as a Calculated Dimension.  No luck.  I still get rows where estimate_is_active and project_is_active are both blank.  Should the "and project..." part also have a Len check?

devarasu07
Master II
Master II

Hi,

Can you share your issue with mock app, it will be easy for us to check and help you easily

Thanks,

Deva

pnn44794
Partner - Specialist
Partner - Specialist
Author

Absolutely.  There are no other expressions or calculated fields.

vishsaggi
Champion III
Champion III

Try this in all your dimensions?

= Aggr(IF( estimate_is_active = 'True' AND project_is_active = 'True', [DM #]), [DM #])

= Aggr(IF( estimate_is_active = 'True' AND project_is_active = 'True', effective_item_qty), [DM #], effective_item_qty)

= Aggr(IF( estimate_is_active = 'True' AND project_is_active = 'True', estimate_is_active), [DM #], estimate_is_active)

= Aggr(IF( estimate_is_active = 'True' AND project_is_active = 'True', project_is_active), [DM #],project_is_active)

...... Let us know if that works?

Anonymous
Not applicable

the easiest way solving this, is using a flag:

LOAD [DM #],
effective_inf_order_date,
[Project #],
[Estimate #],
effective_item_name,
effective_item_qty,
effective_monthly_cost,
estimate_include_in_forecast,
estimate_include_in_budget,
estimate_is_active,
project_is_active,
if (estimate_is_active='TRUE' and project_is_active = 'TRUE','X') as YOURFLAG
FROM [Hide-Row-Sample-Data.xlsx] (ooxml, embedded labels, table is Sheet1);

Then you use set analysis, example:

sum({<YOURFLAG = {'X'}>} effective_monthly_cost)

CarlosAMonroy
Creator III
Creator III

Hi Perry,

Try using the following expression in your table:

=if( len(estimate_is_active) > 0 and estimate_is_active = 'True' and len(project_is_active) > 0 and project_is_active = 'True', MetricField)

Hope that helps,

Carlos M

pnn44794
Partner - Specialist
Partner - Specialist
Author

Hello Robin:

Thanks for the response.  I tried your suggestion, but my script fails to load.  Please note and I believe this makes a difference, I'm actually loading the data from a SQL Server database using SQL Select and not an Excel file.  I provided the Excel file so people could have some sample data.

pnn44794
Partner - Specialist
Partner - Specialist
Author

Hello Vishwarath:

Thanks for your response.  Just to make sure I understand, are you saying that all of the dimensions used should be calculated dimensions using Aggr?  I appreciate your patience as I'm still learning.  Thanks.