topic Re: How to exclude wrong records in QlikView

How to exclude wrong records

Anonymous — Sat, 04 Oct 2014 10:05:51 GMT

Hi all,

I have the following problem.

Let's suppose we have the table below, with 3 machines that have worked for 3 months. For each, there are their working hours.

Machine	Month	Working Hours	Validity
SC01	Jan	10	yes
SC01	Feb	12	NO
SC01	Mar	12	yes
SC02	Jan	14	yes
SC02	Feb	12	yes
SC02	Mar	11	yes
SC03	Jan	12	yes
SC03	Feb	10	NO
SC03	Mar	11	yes

Users can select a Month and a Machine from a list box, in order to see the sum of hours. This is very simple.

The problem is that some data are wrong and these mistakes are registered in column Validity. If, in the period selected by a user, a machine has wrong data (i.e. Validity =NO), even just for a single month, all records for that machine must be ignored and the list box of Machine must not display that machine. So I have to consider only records that have count(Validity='yes')=GetSelectedCount(Month).

Some examples:

1) if a user selects Jan, all data are available --> no problem!

2) if a user selects all months, SC01 and SC03 mustn't be available and their working hours mustn't be considered.

Thanks in advance for your help!

Elena

Re: How to exclude wrong records

Anonymous — Sat, 04 Oct 2014 10:30:26 GMT

Hi,

use in your listboxes instead of field an expression:

=if(not match(Validity, 'NO'), Machine)

in your expressions in the table add set analysis to your formula for example:

sum({< Validity = {yes} >} [Working Hours])

you could also filter the data in script. but this will obviously only work if you dont need not valid machine data in the ui at all.

Best regards

Stefan

Re: How to exclude wrong records

ecolomer — Sat, 04 Oct 2014 10:57:54 GMT

If you present info in a pivot table simple you have all (I think)

Re: How to exclude wrong records

ecolomer — Sat, 04 Oct 2014 11:09:23 GMT

And also with totals for validity

Re: How to exclude wrong records

ecolomer — Sat, 04 Oct 2014 11:12:12 GMT

The qvw file

Re: How to exclude wrong records

Anonymous — Sat, 04 Oct 2014 11:19:52 GMT

Hi,

You may try below script,

Sum({$<Machine=E({1<Validity={NO}>})>} [Working Hours])

Thanks

Re: How to exclude wrong records

maxgro — Sat, 04 Oct 2014 13:40:02 GMT

I think you just have to replace 1 with $ because the Machine to exclude depends on the selection

Sum({$<Machine=E({$<Validity={NO}>})>} [Working Hours])

Re: How to exclude wrong records

Anonymous — Sat, 04 Oct 2014 15:54:19 GMT

Thank for your all suggestions!

@Massimo: your instruction perfectly works with selections on month, but it doesn't work when a machine is selected...

Maybe it's not possible to implement this kind of logic...

Re: How to exclude wrong records

antoniotiman — Sat, 04 Oct 2014 16:11:02 GMT

Hi Elena,

try to add Calculated Dimension

Aggr(If(Validity='yes',Machine),Machine)

and Month

See attachement

Regards,

Antonio

Re: How to exclude wrong records

Anonymous — Sat, 04 Oct 2014 16:17:10 GMT

Thank you Antonio!

It sounds good

Thanks!

Re: How to exclude wrong records

Anonymous — Mon, 06 Oct 2014 10:23:04 GMT

I'm sorry... just another question.

The calculated dimension Aggr(if(Validity='yes', Machine), Machine) works perfectely with table and listbox.

But, if I had also a text object with the expression sum([Working Hours]), how could I implement that logic?

Is it possible?

Re: How to exclude wrong records

er_mohit — Mon, 06 Oct 2014 10:29:03 GMT

Try this

Aggr(sum(if(Validity='yes', [Working Hours])), Machine)

Re: Re: How to exclude wrong records

antoniotiman — Mon, 06 Oct 2014 10:36:35 GMT

See attachement

Re: How to exclude wrong records

jyothish8807 — Mon, 06 Oct 2014 10:41:55 GMT

Hi elena,

Try this:

sum({<Machine={"$(=Aggr(If(Validity='yes',Machine),Machine))"}>}WH)

Regards

Re: How to exclude wrong records

Anonymous — Mon, 06 Oct 2014 11:23:45 GMT

Thank you very much!

Antonio's and Jyothish Kc's expressions have same results but they don't work properly if I select Jan and/or Mar (because, maybe, the "Aggr" function returns more than one Machine, so set analysis doesn't work).

Re: Re: How to exclude wrong records

antoniotiman — Mon, 06 Oct 2014 12:33:52 GMT

New attachment

Re: How to exclude wrong records

Anonymous — Mon, 06 Oct 2014 12:40:38 GMT

Thank you very much, Antonio! You are a magician!

I didn't think it was possible to implent this type of logic.

Thanks!

Re: How to exclude wrong records

Anonymous — Sat, 08 Nov 2014 11:54:42 GMT

Hello!

Using the correct expression suggested above, Aggr(if(Validity='yes', Machine), Machine), in a list box for Machine (in order to see only those with correct data), when a user makes a selection, the other possible values disappear

Is there a way in order the list box to behave as a common list box, i.e. to show all possible values in gray and the selected ones in green?

Thank you very much!

Elena

Re: Re: How to exclude wrong records

antoniotiman — Sat, 08 Nov 2014 17:10:27 GMT

Hi Elena,

I think this is not possible. However You can use List box with Field 'Machine' and

expression like this

Aggr(If(Validity='yes','True'),Machine)

or instead of 'True' use Chr(9733) or other.

See attachment.

Regards,

Antonio

Re: Re: How to exclude wrong records

Anonymous — Fri, 14 Nov 2014 08:47:02 GMT

Thank you! It's a very nice solution, I lIke it very much.

Thanks!

Elena