topic Re: split field into specific sub categories in App Development

split field into specific sub categories

Mon, 12 Jun 2017 08:48:09 GMT

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

Re: split field into specific sub categories

sunny_talwar — Mon, 12 Jun 2017 11:31:05 GMT

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.

Re: split field into specific sub categories

Mon, 12 Jun 2017 12:36:07 GMT

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

sunny_talwar — Mon, 12 Jun 2017 12:38:37 GMT

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

Re: split field into specific sub categories

Mon, 12 Jun 2017 12:40:57 GMT

thanks a lot,

what about the classification process please?

Re: split field into specific sub categories

sunny_talwar — Mon, 12 Jun 2017 12:41:59 GMT

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

Re: split field into specific sub categories

Mon, 12 Jun 2017 12:48:50 GMT

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

sunny_talwar — Mon, 12 Jun 2017 12:53:51 GMT

Can you share the if statement?

Re: split field into specific sub categories

Mon, 12 Jun 2017 15:48:24 GMT

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

Re: split field into specific sub categories

sunny_talwar — Mon, 12 Jun 2017 15:55:06 GMT

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

Re: split field into specific sub categories

Mon, 12 Jun 2017 15:58:11 GMT

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

Re: split field into specific sub categories

sunny_talwar — Mon, 12 Jun 2017 16:13:40 GMT

May be this

MapMotor:

Mapping

LOAD Motor,

'Motor'

Inline [

Motor

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;

Re: split field into specific sub categories

Thu, 15 Jun 2017 09:55:34 GMT

again, thanks a lot Sunny

Re: split field into specific sub categories

sunny_talwar — Thu, 15 Jun 2017 10:57:53 GMT

No problem at all