topic Re: Data suppression in QlikView

Data suppression

jessica_webb — Mon, 05 Oct 2015 16:01:39 GMT

Hi,

I'm looking for a way to suppress values in Qlikview. Feels like it should be possible, but I can't find anything!

Location	Type	Field 1	Field 2	Field 3
North	A	3	31	52
North	B	16	12	1
North	C	51	3	16
North	D	1	16	12
South	A	29	24	3
South	B	2	25	36
South	C	25	1	28
South	D	0	23	21

So for example, if any value in 'Field 1' is fewer than 2, I want to suppress it, and if any value is fewer than 3 in 'Field 2' or 'Field 3' I want to suppress it.

If I filter by location and type, then I would want to suppress anything highlighted red, so that these values are not used in any expressions. However, if I filtered by type only then I would only want to suppress the data in 'Type D' as this is the only place where the value would still be fewer than 2.

Apologies if this makes very little sense - I've started confusing myself slightly!

To give you an idea of the outcome I'm looking for, I'll explain how we're currently doing this:

We look for low values in the data tables in QV (after filtering as necessary), we would then create a new excel file and name it 'Suppressed by location and type' / 'Suppressed by type' and then manually find those values, delete them and then reload the new files.

A very long-winded and error-prone way of doing things!

If anyone is able to offer me any suggestions, I would be very grateful!

Many thanks,
Jess

Re: Data suppression

m_woolf — Tue, 06 Oct 2015 12:03:57 GMT

if([Field 1]<2,null(),[Field 1])) as Field1Temp,

if([Field 2]<3,null(),[Field 2])) as Field2Temp,

if([Field 3]<3,null(),[Field 3])) as Field3Temp,

Drop Fields [Field 1],[Field 2],[Field 3];

rename Fields Field1Temp to [Field 1], Field2Temp to [Field 2], Field3Temp to [Field 3];

Re: Data suppression

jessica_webb — Tue, 06 Oct 2015 14:10:03 GMT

Hi,

Thanks so much for your response. Could you explain it to me a bit?
For example, where in the script would this go?

And how do I tell it what level to suppress at?

Thanks,
Jess

Re: Data suppression

m_woolf — Tue, 06 Oct 2015 14:19:46 GMT

These lines:

if([Field 1]<2,null(),[Field 1])) as Field1Temp,

if([Field 2]<3,null(),[Field 2])) as Field2Temp,

if([Field 3]<3,null(),[Field 3])) as Field3Temp,

would replace the lines in your script where you are loading [Field 1], [Field 2], and [Field 3].

These lines:

Drop Fields [Field 1],[Field 2],[Field 3];

rename Fields Field1Temp to [Field 1], Field2Temp to [Field 2], Field3Temp to [Field 3];

would go after the data is loaded.

And how do I tell it what level to suppress at? I don't understand this question.

Re: Data suppression

jessica_webb — Tue, 06 Oct 2015 14:25:50 GMT

Thank you.

Level to suppress at:

So in my table if I look at each 'Type' by their 'Location', then the fields highlighted red would need to be suppressed. E.g. 'Type C' is suppressed for both 'North' and 'South'. However, if I look at 'Type' only, then the value of 'Type C' would now total 4, so it would no longer need to be suppressed.

The suppression at different levels is really the most crucial part for me.

Thanks,

Jess

Re: Data suppression

m_woolf — Tue, 06 Oct 2015 14:29:59 GMT

The script code that I showed just replaces those Field values with nulls.

Re: Data suppression

jessica_webb — Tue, 06 Oct 2015 15:37:18 GMT

That's what I thought, but thank you for your help.

The suppression really needs to be dynamic based on what's been selected in a list box. Anyone got any ideas?

Thanks,

Jess

Re: Data suppression

jessica_webb — Wed, 07 Oct 2015 13:16:21 GMT

I've now solved this using modifiers in my expressions.