topic Re: create category filed issue in QlikView

create category filed issue

alec1982 — Tue, 19 Jan 2016 18:41:39 GMT

hi,

I have a filed that has

fieldName

sales\layer 1\abc

sales\layer 1\2 \test

calls\layer 1 testing

prod\layer 3\abc

I need a field that shows

layer 1

layer 1\2

layer 1

layer 3

tried wildmatch but run into issue because of having "1/2"

Re: create category filed issue

sunny_talwar — Tue, 19 Jan 2016 18:46:14 GMT

May be this:

Table:

LOAD *,

Mid(fieldName, Index(fieldName, '\', 1)+1, Index(fieldName, '\', -1)-Index(fieldName, '\', 1)-1) as NewField;

LOAD * Inline [

fieldName

sales\layer 1\abc

sales\layer 1\2 \test

calls\layer 1\testing

prod\layer 3\abc

];

Note: I assumed that you forget '\' for the third row. If there is a '\', then this solution will have to be altered further.

(calls\layer 1\testing)

Re: create category filed issue

maxgro — Tue, 19 Jan 2016 18:51:29 GMT

why layer 1 at the second row?

load

if(f1<>f2, mid(field, f1+1, f2-f1-1)) ;

load

field,

index(field, '\', 1) as f1,

index(field, '\', -1) as f2

inline [

field

sales\layer 1\abc

sales\layer 1\2 \test

calls\layer 1 testing

prod\layer 3\abc

];

Re: create category filed issue

alec1982 — Tue, 19 Jan 2016 18:57:45 GMT

thank you all..

actually the field I want to use has the following look

FiledName

AVC - Test Layer 3

Aed Presentation Layer

Adf Layer2

DEF Layer 1/2

QWE Layer 1

the result needs to be

Layer 3

Presentation Layer

Layer 2

Layer 1/2

Layer 1

Re: create category filed issue

sunny_talwar — Tue, 19 Jan 2016 19:05:49 GMT

Is there a logic of how you would select the new field? You picked Presentation, but did not pick Test? Just trying to understand the common theme here

Re: create category filed issue

alec1982 — Tue, 19 Jan 2016 19:08:17 GMT

the word presentation will always be there similar to how I added it

Re: create category filed issue

sunny_talwar — Tue, 19 Jan 2016 19:12:40 GMT

No i meant that

FiledName NewFieldName Notes

AVC - Test Layer 3 Layer 3 Test was not picked

Aed Presentation Layer Presentation Layer Presentation was

From the data it seemed that first word would always be Layer, but Presentation was an exception. Now assuming this was just a sample and the real data might have more exceptions, I am looking for some kind of common theme to be able to code it so that we can derive desired result. Hope what I just said make sense.

Re: create category filed issue

alec1982 — Tue, 19 Jan 2016 19:15:26 GMT

thank you Sunny.. this is the only exception.

We will always have the wording the same all over the data..

FiledName

AVC - Test Layer 3

Aed Presentation Layer

Adf Layer2

DEF Layer 1/2

QWE Layer 1

Re: create category filed issue

sunny_talwar — Tue, 19 Jan 2016 19:26:14 GMT

From what you are saying, It seems like these are the only 5 values that FieldName would ever take. If that's true, may be you just need a mapping load or Pick(Match()) to designate a new value to your existing value. Is my understanding correct?

Re: create category filed issue

alec1982 — Tue, 19 Jan 2016 19:29:10 GMT

actually no. the values Presentation Layer, Layer 1, Layer 1/2, Layer 2, Layer 3 and Presentation Layer wording will always exist on the same location within the string..

the other values within the same string will never be the same..

Re: create category filed issue

sunny_talwar — Tue, 19 Jan 2016 19:32:58 GMT

May be this:

Pick(WildMatch(FieldName, '*Layer 3*', '*Presentation Layer*', '*Layer 2*', '*Layer 1/2*', '*Layer 1*'), 'Layer 3', 'Presentation Layer', 'Layer 2', 'Layer 1/2', 'Layer 1')