topic Re: Filtering Based on Expression Results in QlikView

Filtering Based on Expression Results

limfungkeat — Fri, 26 May 2017 07:34:22 GMT

Customer Name	Focus Product (RM )	All Products ( RM )	Class	Contribution
Total	1,007,350.82	1,136,457.53	A	88.64%
AAA	50,680.50	54,960.50	A	92.21%
BBB	23,925.50	24,920.90	A	96.01%
CCC	22,134.20	22,134.20	A	100.00%
DDD	21,000.00	21,000.00	A	100.00%
EEE	18,451.48	18,835.48	A	97.96%
FFF	17,529.04	23,287.96	A	75.27%
GGG	15,661.60	19,552.60	A	80.10%
HHH	15,659.82	18,312.78	A	85.51%
LLL	13,032.68	13,032.68	A	100.00%
MMM	12,733.80	22,601.85	A	56.34%
NNN	11,169.60	11,169.60	B	100.00%
OOO	10,750.00	10,750.00	B	100.00%
PPP	10,500.00	11,914.40	B	88.13%
QQQ	10,457.74	11,457.74	B	91.27%
RRR	10,436.80	11,084.80	B	94.15%
SSS	10,286.00	11,647.40	B	88.31%
TTT	9,012.00	9,012.00	B	100.00%
YYY	8,990.39	10,312.97	B	87.18%
UUU	8,814.90	9,732.90	B	90.57%
VVV	8,756.00	9,507.08	B	92.10%
ADASD	8,607.80	9,231.80	B	93.24%
ASDSA	8,076.20	9,250.40	B	87.31%
ASDSAD	7,904.50	7,904.50	B	100.00%
ZXCZX	7,800.00	7,800.00	B	100.00%

My dimension is Customer Name and the column Class is an expression If([Exclusive %] <= 0.2, 'A', If([Exclusive %] <=0.70, 'B', If([Exclusive %] <= 0.90,'C',If([Exclusive %] <= 1.0,'D')))). How am i suppose to my this column filterable , so that when i click say one of the class A ...All the class A will shows..As at now, it will only showing one record ...

Re: Filtering Based on Expression Results

Kushal_Chawda — Fri, 26 May 2017 07:43:32 GMT

Go to presentation tab of straight table -> click on Class expression and check "Searchable" option.

Re: Filtering Based on Expression Results

limfungkeat — Fri, 26 May 2017 08:10:32 GMT

Thanks Kushal , apologise for not telling you that i have tried your approach before i posted the question . It just not get filtered correctly . When i did put in the Searchable option and search / click 'A'. The filtered data record is not the same comparewith the original Class A ,before it gets filtered

Re: Filtering Based on Expression Results

zebhashmi — Fri, 26 May 2017 15:03:26 GMT

i can't try it right now, so not sure how it will work

but what about adding a field of Class in Data and filler it from List box

Re: Filtering Based on Expression Results

limfungkeat — Mon, 29 May 2017 02:25:25 GMT

Hi Jahanzeb, the column Class was built base on an expression . It is not a field from any of the table . Tried to do the below , but the field Class is not linked to any of the table / field structure :

CLASS_DEFINITION :
LOAD * INLINE [

CLASS,DEFINITION
A ,   CLASS A,
B,    CLASS B,
C,    CLASS C,
D,    CLASS D

];

Re: Filtering Based on Expression Results

zebhashmi — Mon, 29 May 2017 17:55:59 GMT

r u looking for this

Re: Filtering Based on Expression Results

zebhashmi — Mon, 29 May 2017 18:01:17 GMT

if not then may be you can do

DEFINITION :
LOAD * INLINE [

V1,V2,DEFINITION
0, 0.2, CLASS A,
0.2, 0.7, CLASS B,
];

and use intervalmatch on that.

Re: Filtering Based on Expression Results

Kushal_Chawda — Tue, 30 May 2017 05:01:17 GMT

try like below, create class field in scripr

Data:

LOAD *,

If([Exclusive %] <= 0.2, 'A', If([Exclusive %] <=0.70, 'B', If([Exclusive %] <= 0.90,'C',If([Exclusive %] <= 1.0,'D')))) as Class

FROM table;

Now use this class as dimension in table instead expression

Re: Filtering Based on Expression Results

limfungkeat — Tue, 30 May 2017 09:18:45 GMT

Hi Kushal

Data:

LOAD *,

If([Exclusive %] <= 0.2, 'A', If([Exclusive %] <=0.70, 'B', If([Exclusive %] <= 0.90,'C',If([Exclusive %] <= 1.0,'D')))) as Class

FROM table;

FROM table ...this table is what table ?

Re: Filtering Based on Expression Results

limfungkeat — Tue, 30 May 2017 09:20:39 GMT

The column/ Field [Exclusive %] is not stored in any table, it is constructed base on an expression :

RangeSum(Above(Sum(SALES), 1, RowNo())) / Sum(total SALES)

Re: Filtering Based on Expression Results

Kushal_Chawda — Tue, 30 May 2017 10:51:39 GMT

Please share some sample data so that I can try to implement it from script.

Re: Filtering Based on Expression Results

limfungkeat — Wed, 31 May 2017 01:13:06 GMT

Customer Name	Sales
A	345,660.00
B	343,620.00
C	138,381.00
D	130,212.00
E	127,311.20
F	120,776.00
G	92,351.00
H	73,059.50
I	70,100.00
J	63,118.00
K	60,715.10
L	60,217.50
M	58,308.30
N	57,316.35
O	55,789.30
P	52,970.00
Q	43,176.40
R	40,450.00
S	38,751.70
Total	36,579.80
U	34,900.00
V	28,768.40
W	25,336.90
X	25,115.50
Y	23,120.25
Z	19,200.00
AA	18,424.20
BB	17,036.40
CC	16,700.00
DD	15,382.20
EE	14,072.00
FF	13,704.40
GG	13,250.00
HH	13,170.50
II	11,296.00
JJ	11,007.00
KK	10,524.00
LL	10,420.00
MM	10,374.80
NN	10,311.00
OO	8,910.00
PP	8,650.00
QQ	8,532.00
RR	7,836.60
SS	7,800.00
TT	7,412.90
UU	7,235.55
VV	6,390.00
WW	6,350.00
XX	6,107.80
YY	5,940.00
ZZ	5,909.00
AAABBB	5,895.80
BBB	5,720.00
CCC	4,788.80
DDD	4,595.80
EEE	4,355.00
FFF	3,551.90
GGG	3,510.00
HHH	3,286.00
III	3,100.00
JJJ	3,074.00
KKK	2,880.00
LLL	2,800.00
MMM	2,781.00
NNN	2,695.00
OOO	2,592.00
PPP	2,520.00
QQQ	2,384.35
RRR	2,340.00
SSS	2,294.00
TTT	2,084.00
UUU	2,076.00
VVV	2,000.00
WWW	2,000.00
XXX	1,818.75
YYY	1,760.00
ZZZ	1,678.00
SADS	1,304.00
HGH	1,236.80
SFF	1,170.00
YEYTY	1,104.65
GHGF	1,100.00
XCVXCV	1,071.60
EWERWER	1,044.00
FGFDGDFG	1,001.00
GFGF	1,000.00
WTRRW	1,000.00
FGFDGDFG	999.80
IOUIO	994.40
BN	990.30
GRETERT	988.00
HGJHJ	960.00
GDFG	953.10
TRTER	950.20
XBXV	900.00
SFDSF	874.00
EWRWER	840.00
FGDFG	783.00
HGHG	780.00
EWREW	710.00
DFDGDF	704.00
RETER	680.00
GDFG	640.00
KLJK	600.00
TRTER	595.00
BVCV	540.00
DFDSF	513.00
JHHGJ	500.00
VCBCV	489.00
EFSD	454.60
CVBCV	450.00
TYRTY	402.50
DASD	400.00
JHJK	344.00
WQEWQE	300.00
FGDFGFDG	260.00
TU	210.00
GFGF	200.00
VBCVB	200.00
JKHJK	200.00
EWRWER	177.50
CVXCV	169.40
DWERE	163.00
FSDFS	148.70
EWQEWQE	129.60
SFDSDFD	112.00
WEQWEWQ	111.00
DSFDSF	108.00
WQEWQEQW	108.00
KJHJKJ	100.00
SDSAD	81.40
WEWE	32.60