Data suppression

[jessica_webb]

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

[m_woolf]

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];

[jessica_webb]

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

[m_woolf]

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.

[jessica_webb]

Thank you.

Level to suppress at:

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.

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

[m_woolf]

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

[jessica_webb]

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

[jessica_webb · Accepted solution]

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