Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

split field into specific sub categories

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:

ProductCategoriesColorModelMotor
Ferrari458; red; V12; V6red458V12; V6
BMWM5;blueblueM5
MercedesA 100;  A class; A 300A classA 100; A300
BMWM3; greengreenM8
Renaultclioclio

Note: the categories have no specific orders.

Any any about how to do it?

Many thanks in advance,

Nicolas

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

13 Replies
sunny_talwar

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.

Not applicable
Author

Thanks for your answer Sunny,

unfortunatelly, I have no choice

is it at least possible to divide fields in several columns and classify them?

sunny_talwar

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...

Not applicable
Author

thanks a lot,

what about the classification process please?

sunny_talwar

I have no idea how can you classify them unless you have some kind of logic

Not applicable
Author

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

sunny_talwar

Can you share the if statement?

Not applicable
Author

I don't have any if statement Sunny. I don't know how to do it.

sunny_talwar

Give me the logic that you have in words at least...