Exclude few values from a field/column and render remaining.

[kalyanamharish]

Hi All,

I want to know the list/name of suppliers who do not supply part which has a id 1.

I wanted to show it in a filter pane, i achieved it in SQL but not sure how to do in Qliksense's filter pane.

         SELECT NAME FROM 
		 SUPPLIERMASTER  
		 WHERE SID not IN     
			     (
			      SELECT SID
			      FROM SUPPLYDETAILS 
			      WHERE PID = 1
                 )

Select * from ProductMater
PID	NAME	PRICE	CATEGORY	QTYONHAND
1	PARK EVENUE	39	1	533
2	FIAMA DIWILLS	45	1	422
3	PEARS	        35	1	NULL
4	DOVE	        40	1	368
5	MARGO	        25	1	254
6	LOREAL	        125	2	121
7	GARNIER	        140	2	NULL
8	PONDS	        195	2	134
9	HIMALAYA	350	2	160
10	VIVIL	        600	2	174

Select * from SupplierDetails

PID	SID	DOS	                    CITY	QTYSUPP
9	101	2011-12-25 00:00:00.000	    SIMLA	5
7	102	2011-12-21 00:00:00.000	    NAGPUR	10
6	103	2011-12-15 00:00:00.000	    JAHARKAND	15
8	104	2011-12-05 00:00:00.000	    SRI NAGAR	11
1	105	2011-12-07 00:00:00.000	     HYD	12
8	101	2011-12-03 00:00:00.000	    SIMLA	18
6	102	2011-12-30 00:00:00.000   NAGPUR	5
10	103	2011-12-31 00:00:00.000	  JAHARKAND	8
1	104	2011-12-14 00:00:00.000	  SRI NAGAR	100
2	105	2011-05-25 00:00:00.000	     HYD	11
8	103	2011-05-18 00:00:00.000	   NAGPUR	25
3	103	2011-05-06 00:00:00.000	  JAHARKAND	26
6	101	2011-05-05 00:00:00.000	  SRI NAGAR	5
4	102	2011-11-01 00:00:00.000	   SIMLA	31
5	104	2011-06-26 00:00:00.000	   NAGPUR	25
6	102	2011-07-05 00:00:00.000	  JAHARKAND	8
10	103	2011-11-16 00:00:00.000	  SRI NAGAR	21
8	101	2011-01-30 00:00:00.000	   SIMLA	13
5	103	2012-05-13 00:00:00.000	   NAGPUR	15
7	102	2012-12-12 00:00:00.000	  JAHARKAND	18
6	101	2012-12-12 00:00:00.000	  JAHARKAND	21

Select * from SUPPLIERMASTER
SID	NAME	               CITY	GRADE
101	SRUJANA SUPPLIERS	HYD   	1
102	BHUJANGA SUPPLIERS	SURAT 	2
103	MARUTI SUPPLIERS	CHENAI	3
104	METRO SUPPLIERS	        BANLR 	1
105	BALAJI SUPPLIERS	GURGN 	2

Thanks
Harish.

[Lauri]

Harish, I would recommend you load all of the data into the app, and then filter the data in the visualizations.

One option: 

1. Show all suppliers in a filter
2. Create a useful visual about your suppliers with a measure that excludes the selected supplier(s)

The measure could be

Sum({<SID=e(SID)>} QTYSUPP)

When you select supplier 1 in the filter, the measure will add up QTYSUPP for all suppliers except 1.