How to group sales type and link to Sales table

[hdemissie]

In my sales table I have 5 sales type 1-5, How can I group these sales type to Warehouse and Direct?  Sales Type 1 , 4, 5  group as Warehouse Sales type and 2 and 3 are Direct. When end user select Direct the result will be direct sales type only.

[#2]

Do Warehouse and Direct already exist in your data model?

Assuming they don't, you could create a multi or list box on the following expression:

=if(match(Type, 1,4,5)>0,'Warehouse','Direct')

See attached. Hope that helps.

Matt

[diegofcaivano]

In this case, I would recommend to have that expression previously calculated in the script and then use the new field.

[MK_QSL]

Create a Flag in your script as below...

IF(Match([Sales Types],1,4,5),'Warehouse','Direct') as Flag

[hdemissie · Accepted solution]

Matt, It worked perfect. Thanks for your help.

[#6]

Great, glad it worked!

[hdemissie]

Hello Matt,

  I would like to group these Product Group
(PEWP,WINS,RPLY,FLBR,DPNL,IREB,CRFA,LRFG,DSHE,ESID,SPOD) down to 4 Major area
as 2BM,2DO,2ML,2OT. I try to use the same logic with If match but not producing
the desired result. Please help.

Thank You for your time.

[#8]

Hi Richard,

Can you specify the relationship between 2BM, 2DO, 2ML, etc and the product group, i.e. which products belong to which group? Then I can help with a solution. Also let me know whether you'd like create these groupings in the front end or back end.

Thanks!

Matt

[hdemissie]

Matt,

no relationship between 2BM, 2DO, 2ML and product groups. These are just a name I want to use to group the product groups I listed. I would like to group in back end if possible.

Thanks

RIchard

[#10]

Hi Richard,

Please find an example attached. Here's what's in the script, let me know if that works for you.

Product:
LOAD *
,if(match(Product,'PEWP','WINS','RPLY'),'2BM'
,if(match(Product,'FLBR','DPNL','IREB'),'2DO'
,if(match(Product,'CRFA','DSHE','LRFG'),'2ML'
,if(match(Product,'ESID','SPOD'),'2OT')))) as [Product Group];

LOAD * Inline [
Product
PEWP
WINS
RPLY
FLBR
DPNL
IREB
CRFA
LRFG
DSHE
ESID
SPOD
];