topic Restrict Data in a Straight Table in QlikView

Restrict Data in a Straight Table

hkg_qlik — Mon, 24 Jan 2011 20:18:31 GMT

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,

Restrict Data in a Straight Table

Anonymous — Mon, 24 Jan 2011 20:24:21 GMT

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;

Restrict Data in a Straight Table

Mon, 24 Jan 2011 20:24:30 GMT

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

Restrict Data in a Straight Table

pover — Mon, 24 Jan 2011 20:31:54 GMT

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.

Restrict Data in a Straight Table

pover — Mon, 24 Jan 2011 21:18:57 GMT

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.