topic Re: limited field values based on calculated dimension in QlikView

limited field values based on calculated dimension

s10157754 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: limited field values based on calculated dimension

YoussefBelloum — Wed, 21 Feb 2018 08:58:45 GMT

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.

Re: limited field values based on calculated dimension

s10157754 — Wed, 21 Feb 2018 09:01:55 GMT

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

Re: limited field values based on calculated dimension

s10157754 — Wed, 21 Feb 2018 09:11:02 GMT

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

Re: limited field values based on calculated dimension

Anil_Babu_Samineni — Wed, 21 Feb 2018 11:07:23 GMT

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)

Re: limited field values based on calculated dimension

s10157754 — Thu, 22 Feb 2018 23:26:16 GMT

‌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

Re: limited field values based on calculated dimension

Digvijay_Singh — Fri, 23 Feb 2018 00:36:27 GMT

May be this -

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

Re: limited field values based on calculated dimension

s10157754 — Fri, 23 Feb 2018 08:23:40 GMT

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

Re: limited field values based on calculated dimension

Digvijay_Singh — Fri, 23 Feb 2018 16:01:14 GMT

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

Re: limited field values based on calculated dimension

s10157754 — Sat, 24 Feb 2018 01:26:27 GMT

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

Re: limited field values based on calculated dimension

kakani87 — Sat, 24 Feb 2018 10:40:20 GMT

In Sort tab Move the Frequency to top