topic Re: Filtering on an Expression column on a pivot table in QlikView

Filtering on an Expression column on a pivot table

Wed, 25 Nov 2020 16:16:04 GMT

Hello Qlik Community,

So I created a pivot table and have a few dimension and expression columns that are currently being displayed.

For one of my expression columns, I set a condition where certain values will come up as 1, others as 2. What I want to do is filter that expression coumn so that only the rows that have 1 in the specified column will show up. I do not want the records that return 2 in that column to appear.

Overall, how do I filter on an expression column so that only the rows that show 1 will appear?

Please let me know if any specifics are needed in terms of expamples. I can provide examples if requested.

Thanks in advance,

Tej

Re: Filtering on an Expression column on a pivot table

vishsaggi — Wed, 10 Aug 2016 16:10:20 GMT

Can you provide a sample example or an app with an expected output please ??

Re: Filtering on an Expression column on a pivot table

Wed, 10 Aug 2016 17:34:59 GMT

Hello Vish,

Sure. Here is an example below:

This is a screenshot of the column. The expression for the column is: =IF(Aggr(DISTINCT Count(NUM),NAME,ID,DT)>1,1,0) + IF(Aggr(DISTINCT Count(DT),NAME,ID,NUM)>1,1,0).

So in this column, I would like to apply a filter or maybe an expression to the pivot table so that ONLY '1's will show up on the column, and the '2's will not be shown.

I hope this helps.

Thanks,

Tej

Re: Filtering on an Expression column on a pivot table

vishsaggi — Wed, 10 Aug 2016 18:09:19 GMT

IS it possible to share a sample app please ??

Re: Filtering on an Expression column on a pivot table

Wed, 10 Aug 2016 18:40:44 GMT

Vish,

Unfortunately this is all I can share at the moment. Any suggestions or even vague comments as to what functionality I may need to use to accomplish this task would be good enough as well. I just request some direction as to what function or expression to use and I would be able to figure it out from there.

Hope this helps.

Best,

Tej

Re: Filtering on an Expression column on a pivot table

vinieme12 — Wed, 10 Aug 2016 19:00:00 GMT

why don't you add this condition in set analysis of your measures?

Or Create a flag in your load script to simplify this calculated dimension

Re: Filtering on an Expression column on a pivot table

vinieme12 — Wed, 10 Aug 2016 19:01:34 GMT

Add another iF!

if(IF(Aggr(DISTINCTCount(NUM),NAME,ID,DT)>1,1,0) + IF(Aggr(DISTINCT Count(DT),NAME,ID,NUM)>1,1,0)=1,1,null())

and check suppress when value is null

Re: Filtering on an Expression column on a pivot table

settu_periasamy — Wed, 10 Aug 2016 19:30:51 GMT

Try this

=if (IF(Aggr(DISTINCT Count(NUM),NAME,ID,DT)>1,1,0) +IF(Aggr(DISTINCT Count(DT),NAME,ID,NUM)>1,1,0))=1,1)

Or try

=IF(Aggr(DISTINCT Count(NUM),NAME,ID,DT)>1 XOR Aggr(DISTINCT Count(DT),NAME,ID,NUM)>1,1).

Re: Filtering on an Expression column on a pivot table

Wed, 10 Aug 2016 19:34:40 GMT

Hello Vineeth,

Thanks for your reply! I used the expression you provided and it worked out in only showing the 1s in that column, but do you think I will be able to take it a step further?

Instead of just keeping the 1s in that one column, I was hoping it would keep the whole row where the 1 appears. I think I can better explain this through example:

ID Date Num REST

1 8/5 678 1

2 8/6 917 2

3 8/7 325 2

4 8/8 983 2

5 8/9 53 2

So I have the data set above. What I initally asked was to only show the records where REST =1, which you solved, but I want the whole row with the '1' to show up, so that the data will be brought down to the example below:

ID Date Num REST

1 8/5 678 1

Not only do I need to column REST to show only the '1's, but I need the whole row that contains the 1 to show up. I hope this further explains what I wish to accomplish. Again, this would be of tremendous help to me.

Thank you,

Tej

Re: Filtering on an Expression column on a pivot table

vinieme12 — Wed, 10 Aug 2016 20:08:46 GMT

OK. Why are you creating a pivot table?? what is/are your Expressions?

you could simply add the other fields to the pivot

if you only want to show a data table , use a Straight table as below

Dimensions: ID , Date,Num,Rest

Expression: count({<REST={1}>} REST)

and on the Presentation tab >> Select your expression column and hide it.

Re: Filtering on an Expression column on a pivot table

Wed, 10 Aug 2016 20:26:53 GMT

Thank you for your reply.

Sorry for the fact that I cannot reveal too much about this project due the the sensitivity of the work, but I really prefer to use a pivot table becuase of the expanding capabilities. I have another way that I can go about solving this issue that I hope you can assist me with.

So basically, if the REST columns shows anything less than, or equal to 1, then I need to flag it with the word YES. I currently have an expression that states:

Is there a way I can only show the ones that say 'YES'? I wish I could supress when value is null, but it appears that i can only do that in a calculated dimension, not an expression.

I honestly feel like there is a simple solution to this, but I am overexplaining. If no solution cna be found, I can take the table i currently have and fill it with some sample data and share it with you to better show what I am trying to accomplish. But I wont be able to provide that until tomorrow.

Overall, if you know a way I can supress null values in an expression, not a calculated dimension, that would essentially solve this problem I am having.

Sorry for all the confusion and as always, thanks for your replies.

Best,

Tej

Re: Filtering on an Expression column on a pivot table

vinieme12 — Wed, 10 Aug 2016 20:59:23 GMT

Yes, It would be easier to demonstrate with a sample app containing sample data.

Thanks