Filter 1 field based by selection from 2 different fields

[SAM81]

I have a data table set as such

ID           Category               Sale

1              Phone                    10

1              Broadband           15

1              Router                     20

1              Stylus                      6

2              Phone                    6

2              Broadband           8

2              Router                     20

2              Stylus                      4

There are 2 different filters within my sheet with different names.  Lets call them Product and Accessory

I would like the user to be able to select values from these filters independently but both selections apply to the Category field in the table above.

Eg if the user selected Phone from the Product field and Stylus from the accessory field this would return

ID           Category               Sale

1              Phone                    10

1              Stylus                      6

2              Phone                    6

2              Stylus                      4

Hope this makes sense but is it possible?

[sidhiq91]

@SAM81  You can create a script as shown below in the back end.

NoConcatenate
Temp:
Load * inline [
ID,Category,Sale

1,Phone , 10

1,Broadband, 15

1,Router,20

1,Stylus,6

2,Phone,6

2,Broadband,8

2,Router ,20

2,Stylus ,4
];

NoConcatenate
Temp1:

Load *,
if(Match(Category,'Phone','Stylus'),'Product', if(match(Category,'Broadband','Router'),'Accessory')) as Category_name
Resident Temp;

Drop table Temp;

Exit Script;

This should resolve your issue.

[Digvijay_Singh]

Do you have separate fields Product and Accessory in your data model, if yes then how they are connected to the table having Category field. OR they are calculated fields defined in the filter with some expression.

[edwin]

normally you model your object as a product by itself or as an accessory and you capture those attributes as separate fields. 

however, working with your current data model and answering from a strictly technical perspective, you build a link between your fact (your table above) and your two tables (assuming they are separate tables) Products and ACcessories:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';


data:
load * inline [
ID,Category,Sale
1,Phone,10
1,Broadband,15
1,Router,20
1,Stylus,6
2,Phone,6
2,Broadband,8
2,Router,20
2,Stylus,4
3,Camera, 5
];

NoConcatenate
Products:
load * inline [
Product
Phone
Router
Broadband];

NoConcatenate
Accessories:
load * inline [
Accessory
Stylus
Camera];

NoConcatenate
tmpLink:
load Product Resident Products;

inner join (tmpLink) load Accessory resident Accessories;

NoConcatenate
Link:
load 
Product as Category, Product, Accessory
Resident tmpLink;


Concatenate (Link)
load 
Accessory as Category, Product, Accessory
Resident tmpLink;

drop table tmpLink;

with no selection:

with selections: