Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Partner
Partner

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.

Tags (2)
12 Replies
devarasu07
Honored Contributor II

Re: Hiding Rows Based On Blank Fields

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

Partner
Partner

Re: Hiding Rows Based On Blank Fields

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
Honored Contributor II

Re: Hiding Rows Based On Blank Fields

Hi,

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

Thanks,

Deva

Partner
Partner

Re: Hiding Rows Based On Blank Fields

Absolutely.  While you were responding, I was getting some sample data together.    I've attached an Excel spreadsheet with some sample data.  It's not all columns, but I believe it's the key columns.  Please note, DM # is also used as an expression (just [DM #]).  The data is coming from a SQL Server database.  There are no other expressions or calculated fields.

vishsaggi
Esteemed Contributor III

Re: Hiding Rows Based On Blank Fields

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?

robin_hausdoerfer
Valued Contributor III

Re: Hiding Rows Based On Blank Fields

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

Re: Hiding Rows Based On Blank Fields

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

Partner
Partner

Re: Hiding Rows Based On Blank Fields

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.

Partner
Partner

Re: Hiding Rows Based On Blank Fields

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.