topic Re: Display only Rows with values on all Expressions in QlikView

Display only Rows with values on all Expressions

Anonymous — Thu, 16 Jun 2016 16:36:42 GMT

Hello,

I would like to display only rows into the Straight Table where Values are presents in all Expressions:

How can i do that please ?

Thank you in advance for your help,

Sébastien.

Re: Display only Rows with values on all Expressions

swuehl — Thu, 16 Jun 2016 16:41:30 GMT

You can create a hidden expression t(labeled COND ) that checks that all expressions return something:

=Len(Trim( Only(FTE) )) AND Len(Trim( Only(Path) )) AND Len(Trim( Concat(Job) ))

adapt to your expressions used.

Then change all your existing expressions like

=If( "COND", YourCurrentExpression)

And enable 'suppress zero values' on presentation tab.

Re: Display only Rows with values on all Expressions

Anonymous — Thu, 16 Jun 2016 16:44:51 GMT

A Hidden Expression ?!?

How can i do that please ?

Re: Display only Rows with values on all Expressions

swuehl — Thu, 16 Jun 2016 16:51:04 GMT

Basically create an expression on expression tab and set this expression column to hidden on presentation tab.

edit:

You can also do the same by repeating the conditional statement in each If() condition of each expression.

Re: Display only Rows with values on all Expressions

Anonymous — Thu, 16 Jun 2016 16:55:34 GMT

Sorry, but I do not see the option to hid the Expression 😕

Re: Display only Rows with values on all Expressions

swuehl — Thu, 16 Jun 2016 16:57:57 GMT

Re: Display only Rows with values on all Expressions

Anonymous — Thu, 16 Jun 2016 17:33:38 GMT

Ok, thanks for your help but do not see the Option:

Then, I write this for each Expression but it doesn't work:
If(Len(Trim( Only(Region) ))<>0,Region)

Do you have an idea please ?

Re: Display only Rows with values on all Expressions

swuehl — Thu, 16 Jun 2016 22:45:55 GMT

You don't see this option to hide the expression because you are using a pivot table chart, not a straight table chart.

But I think you should still be able to remove the lines where not all expressions return a value.

Could you post all your current expressions?

Or could you create a small sample QVW and upload this to the thread?

Re: Display only Rows with values on all Expressions

idogridish2 — Thu, 16 Jun 2016 23:08:58 GMT

You can create a calculated dimension with an if statement that checks if all expressions returns a value,

and use the "suppress when value is null" on this field in dimensions tab.

how big is your model? and is it already slow? - you can try the solution and decide after.

best regards.

Re: Display only Rows with values on all Expressions

Anonymous — Fri, 17 Jun 2016 07:30:19 GMT

Ok, here my hidden expression:

"=Len(Trim(Only(Nom_DHACCOR))) <> 0 And Len(Trim(Only(Employee_CD))) <> 0 And Len(Trim(Only(Region))) <> 0 And Len(Trim(Only(Fte))) <> 0 and Len(Trim(Only(Path))) <> 0 and Len(Trim(Only(FTC_Nm))) <> 0"

And all my Expression here:

Thanks in advance for your help.

Sébastien.

Re: Display only Rows with values on all Expressions

swuehl — Fri, 17 Jun 2016 08:33:34 GMT

As first step, unhide this new expression and check that it shows -1 on all lines where each original expression shows a value and 0 if at least one original expression does not show a value.

If this is successful, label this expression COND,

Then e.g. for your first original expression, write:

=If( "COND", Only(Nom_DHACCOR))

For the second:

=If("COND", Only(Employee_CD) )

etc.

Now you should only see the lines with COND =-1, if 'suppress zero values' is enabled on presentation tab.

Now hide the expression column COND on presentation tab.

Re: Display only Rows with values on all Expressions

Anonymous — Fri, 17 Jun 2016 08:38:54 GMT

I do not understand, i just see this now:

Re: Display only Rows with values on all Expressions

swuehl — Fri, 17 Jun 2016 08:47:24 GMT

You see this just doing the first step?

Re: Display only Rows with values on all Expressions

Anonymous — Fri, 17 Jun 2016 08:48:13 GMT

Yes 😕

Re: Display only Rows with values on all Expressions

Anonymous — Fri, 17 Jun 2016 12:54:44 GMT

Do you have an idea them ?

Re: Display only Rows with values on all Expressions

swuehl — Fri, 17 Jun 2016 13:00:03 GMT

Seems to work for me,have a look at this demo

Re: Display only Rows with values on all Expressions

Anonymous — Fri, 17 Jun 2016 13:17:06 GMT

Than you a lot, but how can i suppress the first row where there are only '-' values please ?

Re: Display only Rows with values on all Expressions

swuehl — Fri, 17 Jun 2016 13:18:29 GMT

That's the total line.

Go to expression tab and select 'No totals' for each expression in option section 'Total mode'.

Re: Display only Rows with values on all Expressions

Anonymous — Fri, 17 Jun 2016 13:21:59 GMT

Then i have a problem i do not have the same result than my Table box:

Table Box values:

Straight Table Values:

the the lines with the same Matricule dissapear in the straight table.

Do you have an idea ?

Sébastien.

Re: Display only Rows with values on all Expressions

swuehl — Fri, 17 Jun 2016 13:34:05 GMT

If you want to see two lines for the same Matricule, you would need to add a dimension to group your data into a finer granularity, e.g.using FTE (two distince values per GL001348465 Matricule).