Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

Re: split field into specific sub categories

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;

13 Replies

Re: split field into specific sub categories

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

Re: split field into specific sub categories

Thanks for your answer Sunny,

unfortunatelly, I have no choice

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

Re: split field into specific sub categories

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

Re: split field into specific sub categories

thanks a lot,

what about the classification process please?

Re: split field into specific sub categories

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

Not applicable

Re: split field into specific sub categories

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

Re: split field into specific sub categories

Can you share the if statement?

Not applicable

Re: split field into specific sub categories

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

Re: split field into specific sub categories

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

Community Browser