limited field values based on calculated dimension

[s10157754]

Dear Qlikview Experts,

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

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

[YoussefBelloum]

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]

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]

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)

[s10157754]

‌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 · Accepted solution]

May be this -

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

[s10157754]

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]

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...

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

Best Regards