Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
again, thanks a lot Sunny
No problem at all