Filter table based on it's values

[#1]

Hi guys

I have a table with the following columns:

ID, Col1, Col2

I have a lot of rows in it, I want to filter the table only where Col1=Col2.

I have tried doing this by using Set Analysis, but I haven't succeeded.

Also, I am trying to accomplish this by a button, which means that I am trying to use an Action, but which one should I use? Select In Field?

Thanks for your help!

[whiteline]

Hi.

You can't use set analysis in such a case, because it is calculated once for the entire chart.

You could do it using calculated dimension:

=aggr(if("Col1=Col2", ID), ID)

And toggle checkbox 'supress whten value is null'.

Of course you should replace "Col1=Col2" with appropriate test condition.

[#3]

try this

=if(col1=col2,count(id))

Regards,

Kabilan K.

[#4]

Thanks guys,

My next question is how do I toggle this by using a button?

Which Action (and on which field) should I use ?

If I use a Clucluated Dimention as a Dimention on my table, it will not

give me the desired result.

Many thanks,

[qlikpahadi07]

well its Simple you can do it in Script like

if(Col1 = Col2, 'Same','Different') as Flag,

then you will have a Flag with two Options Select 'Same it will filter your data '

[whiteline]

Action 'select in field'.

Using the same condition

="Col1=Col2"

[Oleg_Troyansky]

Sometimes I wish answers were moderated for correctness, not just for spam...

- For large tables with a lot of data, I would never recommend to use sum(if(...)) constructions for their poor performance.

- The other construction if(col1=col2, count(id)) simply won't return any meaningful results because col1 and col2 can't be uniquely identified outside of the aggregation function.

Rohit's answer is the closest - you should calculate the Flag in the script. Now you can either use selection in the Flag field to force Same values or different values, or you can replace your whole expression with a different expression using an action that can be triggered by the button:

sum( Value)

     vs.

sum ({<Flag = {'Same'}>}   Value)

Read the following blog article for an example of using Variables to make your formulas more flexible:

Q-Tip #6 - Those Tricky $-Sign Expansions | Natural Synergies

cheers,

Oleg Troyansky

www.masterssummit.com

[whiteline]

The person who asks moderates the answers for correctness.

We can only guess what statements correspond to the names col1 and col2.

Suppose that there are some complex conditions with some aggregations, that dynamically depend on user selection and your solution with flag in script become not so elegant and easy.

Of course separate flag is worth to achieve.

But one size doesn't fit all. Sometimes even sum (Value*Flag) is preferable than sum ({<Flag = {'1'}>} Value)

[Oleg_Troyansky]

Sometimes even sum (Value*Flag) is preferable than sum ({<Flag = {'1'}>} Value)

whiteline - I would be very curious to see an example of that. I have a lot of evidence, both theoretical and practical, suggesting the opposite. I've yet to see an example where sum(Value*Flag) could be preferable to sum({<Flag={1}>} Value)

Please, explain what you meant.

cheers,

Oleg Troyansky

[whiteline]

Oleg, I know that sets should have better performance. But sometimes it's not about performance.

The first that come to mind is a complex task where nested $-expansion with parameters is used. The expression stored in a field is used to change calculations dynamicaly depending on what expression the user selects. $-expansion fails with set syntax.

Second, I've done some 50M tests and the multiplication is not so bad, especially when you don't want to explain the users what means that strange bracets.

Third, there was thread here where multiplication was better in reality.