topic Re: Hiding Rows Based On Blank Fields in QlikView

Hiding Rows Based On Blank Fields

pnn44794 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Hiding Rows Based On Blank Fields

devarasu07 — Mon, 27 Mar 2017 18:02:14 GMT

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

Re: Hiding Rows Based On Blank Fields

pnn44794 — Mon, 27 Mar 2017 18:23:49 GMT

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?

Re: Hiding Rows Based On Blank Fields

devarasu07 — Mon, 27 Mar 2017 18:28:50 GMT

Hi,

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

Thanks,

Deva

Re: Hiding Rows Based On Blank Fields

pnn44794 — Sun, 13 Oct 2019 18:27:29 GMT

Absolutely. There are no other expressions or calculated fields.

Re: Hiding Rows Based On Blank Fields

vishsaggi — Mon, 27 Mar 2017 18:57:11 GMT

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?

Re: Hiding Rows Based On Blank Fields

Anonymous — Mon, 27 Mar 2017 19:12:43 GMT

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)

Re: Hiding Rows Based On Blank Fields

CarlosAMonroy — Mon, 27 Mar 2017 19:23:25 GMT

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

Re: Hiding Rows Based On Blank Fields

pnn44794 — Mon, 27 Mar 2017 20:53:37 GMT

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.

Re: Hiding Rows Based On Blank Fields

pnn44794 — Mon, 27 Mar 2017 20:55:43 GMT

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.

Re: Hiding Rows Based On Blank Fields

vishsaggi — Mon, 27 Mar 2017 21:12:47 GMT

Technically yes, or if you don't have any typical expressions to be used, just add few dimensions with Aggr and add rest of your dimensions in your expression tab as your normal dimensions fields ?

Re: Hiding Rows Based On Blank Fields

pnn44794 — Mon, 27 Mar 2017 21:37:44 GMT

Hello Carlos:

Thanks for your reply. I tried your suggestion above and after I removed the other expression which was just simply [DM #], it appears to have worked! I did expect more rows than what I'm seeing to be returned, so I may still have something wrong on my side. I will look at it further tonight and tomorrow if need be. Thank you. I appreciate the help.

Re: Hiding Rows Based On Blank Fields

Anonymous — Tue, 28 Mar 2017 03:54:15 GMT

When using a preceding load in combination with sql selects there shouldn't be any difference.

So please post your script...