Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Find Value in Text and "Vlookup" value

Dear all,

I hope you had all a good start into the week... I have the following problem where I would need your brains :

I would like to find a value (forced package) in a text and "vlookup" the Code & Package value.

The text is the following for the MCode ABCDEF: "A32, W98, XXD, BCD, W11"

As a result it should show me "ABCDEF - Pack.2"

So it has to recognize the right MCode and find the forced package code in the text to give me the right Code & Package value.

MCodeForced PackageCode & Package
ABCDEFW94ABCDEF - Pack.1
ABCDEFW98ABCDEF - Pack.2

What kind of formula do I have to use to get to this result?


Thanks for any kind of hints and solutions!!

Cheers

Martin

Tags (3)
1 Solution

Accepted Solutions
Highlighted
ramoncova06
Valued Contributor III

Re: Find Value in Text and "Vlookup" value

this seems to work

View solution in original post

20 Replies
Highlighted

Re: Find Value in Text and "Vlookup" value

Not entirely sure what you requirement is, would you be able to share a sample?

Best,

Sunny

Highlighted
MVP
MVP

Re: Find Value in Text and "Vlookup" value

Create a mapping table and then ApplyMap to perform the lookup:

MapCodes:

Mapping LOAD [Forced Package], [Code & Package]

FROM ....

Data:

LOAD ....

     [Forced Package],

     ApplyMap('MapCodes', [Forced Package]) As [Code & Package],

     ....

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted

Re: Find Value in Text and "Vlookup" value

You can use a formula in your expression, somethin like:

If(MCode='ABCDEF' and subfield(myString, ',' ,2)='W98', MCode & 'Pack.1',

....

)

But I think it should be better to define a mapping table and link it to MCode and "Forced Package" string in the script.

Highlighted
senpradip007
Valued Contributor III

Re: Find Value in Text and "Vlookup" value

PFA. Hope it will help you.

Highlighted
Not applicable

Re: Find Value in Text and "Vlookup" value

Sorry to be a bit unclear...


the text is always different and is called PRCodes and it is different even for the same MCode

so it should look up for the right MCode if the Forced Package is part of the PRCode and give me the Code & Package as a result

e.g.

MCode          PRCodes                                   Code & Package (Result I am looking for)

ABCDEF       A32, W98, XXD, BCD, W11         ABCDEF - Pack.2

ABCDEF       A38, W94, XFD, AAD, W11        ABCDEF - Pack.1

123456          A38, W94, XFD                         "missing combination"

Highlighted
vvvvvvizard
Contributor III

Re: Find Value in Text and "Vlookup" value

in the script

=subfield(theStringFieldname,':',1) as Mcode,

=subfield(theStringFieldname,',',2) as [Forced Package],

the first value not in bold needs to exactly match the field where you matching the value to pull the code and package value , just like in excel .

=lookup(concatenate(subfield(theStringFieldname,':',1),subfield(theStringFieldname,',',2) , Concatenate(matchfieldname)&Concatenate(the_other_matchfieldname), matchfieldvalue [, tablename])

Highlighted
Not applicable

Re: Find Value in Text and "Vlookup" value

Sorry but unfortunately I do not understand how to solve it

could you please explain it a bit more detailed?
sorry for bothering you all with that ...

Highlighted
danieloberbilli
Valued Contributor II

Re: Find Value in Text and "Vlookup" value

I would suggest a mapping as Jonathan Dienst already mentioned.

Please find an example below and the .qvw attached.

DataMap:

Mapping LOAD

Key,

[Code & Package (Result I am looking for)]

;

LOAD *,

MCode &'_'& PRCodes as Key

;

LOAD * INLINE [

    MCode, PRCodes, Code & Package (Result I am looking for)

    ABCDEF, "A32, W98, XXD, BCD, W11 ",  ABCDEF - Pack.2

    ABCDEF, "A38, W94, XFD, AAD, W11  ",  ABCDEF - Pack.1

    123456, "A38, W94, XFD  ", missing combination

];

Data2:

LOAD *,

ApplyMap('DataMap', Key,'unknown') as MappedValues

;

LOAD *,

MCode &'_'& PRCodes as Key

;

LOAD * INLINE [

    MCode, PRCodes,

    ABCDEF, "A32, W98, XXD, BCD, W11 "

    ABCDEF, "A38, W94, XFD, AAD, W11  "

    123456, "A38, W94, XFD  "

];

Highlighted
Not applicable

Re: Find Value in Text and "Vlookup" value

Thank you so much so far for your help.

Unfortunately it hasn´t worked so far...

maybe because the data is coming from different sources...

source 1:

MCode      Forced Package                               Code & Package (Result I am looking for)

ABCDEF     W98                                                 ABCDEF - Pack.2

source 2:

MCode      PRCodes       

ABCDEF     A32, W98, XXD, BCD, W11 

Thanks and regards

Martin