split field into specific sub categories

[#1]

Hello all,

I'm struggling defining two subcategories depending on one field, during the LOAD process.

The objective is quite simple (in bold) and should be classified thanks to "ifs" functions:

Product	Categories	Color	Model	Motor
Ferrari	458; red; V12; V6	red	458	V12; V6
BMW	M5;blue	blue	M5
Mercedes	A 100;  A class; A 300		A class	A 100; A300
BMW	M3; green	green	M8
Renault	clio		clio

Note: the categories have no specific orders.

Any any about how to do it?

Many thanks in advance,

Nicolas

[#2]

here is an example:

if [Categories] contains ('V6' or 'V12' or 'A100' or 'A200') then copy the subfield value to the [motor] column,

else

if [Categories] contains ('red' or 'blue'') then copy the subfield value to the [color] column,

else

copy the subfield value to the [model] column

[sunny_talwar · Accepted solution]

May be this

MapMotor:

Mapping

LOAD Motor,

  'Motor'

Inline [

Motor

V6

V12

A 100

A 300

];

MapColor:

Mapping

LOAD Color,

  'Color'

Inline [

Color

red

blue

green

];

Table:

LOAD *,

  ApplyMap('MapMotor', Categories, ApplyMap('MapColor', Categories, 'Model')) as Mapping;

LOAD RecNo() as SNo,

  Product,

  Trim(SubField(Categories, ';')) as Categories;

LOAD * INLINE [

    Product, Categories

    Ferrari, 458; red; V12; V6

    BMW, M5;blue

    Mercedes, A 100;  A class; A 300

    BMW, M3; green

    Renault, clio

];

FinalTable:

LOAD Distinct SNo,

  Product

Resident Table;

FOR i = 1 to FieldValueCount('Mapping');

  LET vField = FieldValue('Mapping', $(i));

  Left Join (FinalTable)

  LOAD Distinct SNo,

  Product,

  Concat(Categories, '; ') as [$(vField)]

  Resident Table

  Where Mapping = '$(vField)'

  Group By SNo, Product;

NEXT

DROP Table Table;

[#4]

again, thanks a lot Sunny

[sunny_talwar]

No problem at all