how to group the model into a series

[wendytham]

Hi,

I have model and sales data.

I want to group the model data into 2 series.

Eg. AB-12TH100A = TH100 series

      AB-13TH100B = TH100 series

     AB-12T300A = T300 series

     AB-12T300B = T300 series

Im using the below script to group the data, but it doesn't work well.

If(Index(Model,'FS500'), 'FS500 Series', If(Index(Model,'F400'),'F400 Series')) as Model_Series

How if i have DZ-12TH100A, TU-12TH100B, etc, but i do not want to include them as i want the model start with

AB-*TH100* only?

Thank you.

[juraj_misina]

Hi Wendy,

yes, you can use WildMatch() function:

If(WildMatch(Model, 'AB-*TH100*'), 'TH100') as Model_Series

Plus, instead of writing complex nested if for your purpose, you can combine it with Pick function

Pick(WildMatch(Model, 'AB-*TH100*', 'AB-*FS500*', 'AB-*T300*'), 'TH100', 'FS500', 'T300') as Model_Series

Hope this helps

Juraj

[Anil_Babu_Samineni]

The Above condition provided by me from last thread.

Can we know what is input and output field in excel to better understand?

[wendytham]

Hi, thank you for your help.

After grouping them into series, how to group those series into another group as below?

"TH-100", "TH-200", "TH-300" = TH series

"T-100", "T-200", "T-300" = T series

[juraj_misina · Accepted solution]

Hi,

using the same logic:

Pick(WildMatch(Model, 'AB-*TH*', 'AB-*T*'), 'TH Series', 'T Series') as Series