Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
s10157754
Creator III
Creator III

limited field values based on calculated dimension

Dear Qlikview Experts,

I have 2 fields, 'Machine No' and 'Result'. (The values under these 2 fields can be repeated)

Capture1.PNG

I was planning to create multiple list boxes/tables to display possible values under 'Result' field for different machine no. At the same time, I also want to exclude certain possible values such as 'Intermittent' and 'Moderate' from showing. So the result field will only display if:

'Machine No' 100 --->Good

'Machine No' 200 --->Bad, Good

'Machine No' 300 --->Perfect

'Machine No' 400 --->Bad

'Machine No' 500 --->Perfect

May I know how can I achieve this using list box or table chart? Thank you so much for your time!

Best Regards

1 Solution

Accepted Solutions
Digvijay_Singh

May be this -

=Aggr(Only({<Machine No={100},Result-={'Intermittent','Moderate'}>} Result), Result)

View solution in original post

10 Replies
YoussefBelloum
Champion
Champion

Hi,

you can filter on a TextBox like this:

=if(not Match([Machine No],'Intermittent'), [Machine No])


On a TableBox I don't think you can filter.

s10157754
Creator III
Creator III
Author

Hi Qlikview Experts,

I had tried to create straight table chart and put 'Result' as my dimension and my expression I set it to be:

=Aggr(Only({<Machine No = {100}>} Result), Result)

This works for showing all possible values under result column for machine no 100. But how can I restrict and limit the result column only to show 'Good' instead of 'Good' & 'Moderate'?

Best Regards

s10157754
Creator III
Creator III
Author

Dear Belloum,

I think I didn't make my question clearly. I would like to show all possible values under result column for different machines. (Excluding 'Intermittent' and 'Moderate') So in this case:

if machine no is 100, result column should display: Good

if machine no is 200, result column should display: Good

                                                                                 Bad

if machine no is 300, result column should display: Perfect

if machine no is 400, result column should display: Bad

if machine no is 500, result column should display: Perfect

Best Regards

Anil_Babu_Samineni

Perhaps this? This is just assumption, Even i am in wrong direction may be explanation needed?

If(Pick(Match(machine, '100', '200', '300', '400', '500'), 'Good', 'Good', 'Perfect', 'Bad', 'Perfect') and Not WildMatch(machine, 'Intermittent', 'Moderate'), 1,0) as FieldName

And Expression as follows?

=Aggr(Only({<FieldName = {1}>} Result), Result)


Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
s10157754
Creator III
Creator III
Author

‌Dear Anil,

I would like to apologize for my late reply.

Basically I have created multiple Straight Tables for all the machines. (1 table for 1 machine) The dimension I put it as ‘Result’ first, because I going to hide my dimension column as it is unnesscary for me. So left with expression column, the expression I was using in first straight table for machine 100 is:

=Aggr(Only({<Machine No={100}>} Result), Result)

So at this point, my first straight table is able to show all the possible values under Result column if machine no is 100. However, some of the results such as “Intermittent” and “Moderate” etc have also been loaded into Qlikview Data Model, so I do not wish to show these 2 results anymore and I want to exclude all those rows contained “Intermittent” and “Moderate”.

Thank you so much for the help!

Best Regards

Digvijay_Singh

May be this -

=Aggr(Only({<Machine No={100},Result-={'Intermittent','Moderate'}>} Result), Result)

s10157754
Creator III
Creator III
Author

Dear Digvijay,

Thanks a ton for your help! Your solution works for me.

In addition, I had added one more column beside to show the frequency of the result. The expression I was using was listed below:

=count({<[Machine No] = {100},Result-={'Intermittent','Moderate'}>} [Result])

This expression works and was able to show the number of counts of the result. However, I was unable to arrange the data in descending format, if able to rearrange the data displayed in descending format, it will be much easier for the user to analysis the data statistics. Any ideas from you would be greatly appreciated!

Best Regards

Digvijay_Singh

Did you try options in sort tab, can you share the image of straight table and sort options to understand how it looks like. Want to understand if you are trying to sort by dimension or measure. There are plenty of options in sort tab to choose as per the needs

s10157754
Creator III
Creator III
Author

Dear Digvijay,

My apologies for the late reply. I tried to put all the frequency options under the sort tab as descending. But it does not work...

Capture.PNG

The attached QVW file for your reference. Thank you so much for the help!

Best Regards