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
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;
There needs to be some sort of logic to code this... without no specific order and specific logic, I don't think this can be done in QlikView.
Thanks for your answer Sunny,
unfortunatelly, I have no choice
is it at least possible to divide fields in several columns and classify them?
You can divide them in columns using SubField function
SubField(Categories, ';', 1) as Col1,
SubField(Categories, ';', 2) as Col2,
SubField(Categories, ';', 3) as Col3,
... and so on...
thanks a lot,
what about the classification process please?
I have no idea how can you classify them unless you have some kind of logic
yes Sunny, actually it is with an if function
For instance:
if 'V6' or 'V12' or 'A100' or 'A200' then copy value to the motor column
Can you share the if statement?
I don't have any if statement Sunny. I don't know how to do it.
Give me the logic that you have in words at least...