Restrict Data in a Straight Table

[hkg_qlik]

Hi,

I have a straight Table as following:

Division	Branch	Account No.	Customer	Daily Sales	Tons	Gross Profit	Grs Pft/Ton	MTD	YTD
A	1	601	ABC	1,000	2	1,138	99.00	3,210	3,210
B	2	849	EFG	321	4	228	100.00	321	321
C	3	294	HIJ	840	5	671	11.00	1,608	1,608
D	4	806	LMN	449	3	362	22.00	1,346	1,346
E	5	171	OPQ	325	5	241	44.00	1,195	1,195
A	1	271	RST	1,200	1	1,867	66.00	55,802	55,802
B	2	391	UVW	0	0	413	120.00	10,075	10,075
C	3	573	XYZ	0	0	311	121.00	870	870

I want to restrict the data by : Daily Sales Column >1000 and Grs Pft/ Ton < 99.00

Thanks,

H

[Anonymous]

If you want to do this in the load script you can try to add something like the next line to your load:

WHERE [no.Customer] >1000 AND [Grs Pft] <99;

[#3]

Use Calculated Dimension

if([Daily Sales Column] >1000,[Daily Sales Column])

if([Grs Pft]/Ton <99.00,[Grs Pft]/Ton)

enable Suppress when value is null

Try if this works

[pover]

In every column you have to put if((Daily Sales Expression) >1000 and (Grs Pft/ Ton Expression) < 99.00, (Column Expression)) which will return null if the requirements are not met and QlikView will by default suppress rows with all null values, or by ticking the option "Suppress Null Values" in the presentation tab.

To save time and effort you can put the conditions of the if statement in a variable. For example, the variable vCondition could be

(Daily Sales Expression) >1000 and (Grs Pft/ Ton Expression) < 99.00

And in every column expression put

if($(vCondition), Column Expression)

Regards.

[pover]

If you would do it with calculated dimensions, you have to use the aggr() function like the following example:

aggr(if([Daily Sales espression] >1000 and [Grs Pft/Ton expression] < 99,[Daily Sales Column]),Division,Branch,[Account No.])

although I don't recommend ever using calculated dimensions unless it is absolutely necessary.

Regards.