topic Re: Find Value in Text and "Vlookup" value in QlikView

Find Value in Text and "Vlookup" value

Tue, 26 May 2015 10:31:47 GMT

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.

MCode	Forced Package	Code & Package
MCode	Forced Package	Code & Package

ABCDEF	W94	ABCDEF - Pack.1
ABCDEF	W98	ABCDEF - 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

Re: Find Value in Text and "Vlookup" value

sunny_talwar — Tue, 26 May 2015 10:40:47 GMT

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

Best,

Sunny

Re: Find Value in Text and "Vlookup" value

jonathandienst — Tue, 26 May 2015 10:41:26 GMT

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],

....

Re: Find Value in Text and "Vlookup" value

alexandros17 — Tue, 26 May 2015 10:43:47 GMT

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.

Re: Find Value in Text and "Vlookup" value

senpradip007 — Tue, 26 May 2015 10:58:47 GMT

PFA. Hope it will help you.

Re: Find Value in Text and "Vlookup" value

Tue, 26 May 2015 11:20:09 GMT

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"

Re: Find Value in Text and "Vlookup" value

vvvvvvizard — Tue, 26 May 2015 11:28:25 GMT

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])

Re: Find Value in Text and "Vlookup" value

Wed, 27 May 2015 04:10:32 GMT

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

Re: Find Value in Text and "Vlookup" value

danieloberbilli — Wed, 27 May 2015 05:00:00 GMT

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  "
];

Re: Find Value in Text and "Vlookup" value

Thu, 28 May 2015 12:44:26 GMT

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

Re: Find Value in Text and "Vlookup" value

Mon, 01 Jun 2015 09:25:15 GMT

No one has an idea how to solve this?

Re: Find Value in Text and "Vlookup" value

ramoncova06 — Mon, 01 Jun 2015 14:15:22 GMT

so you first want to find the Forced Package and then based on that you want to pull the Code & Package ?

I do think that you can do this by using applymap as Daniel and Jonathan already suggested

it might be easier if you provided an example with more details

Re: Find Value in Text and "Vlookup" value

Tue, 02 Jun 2015 04:59:48 GMT

yes exactly that is what I want to do... I will prepare an example ...

Re: Find Value in Text and "Vlookup" value

Tue, 02 Jun 2015 05:31:18 GMT

here as promised the examples... when I select a unique code it should show one package in the left table

Re: Find Value in Text and "Vlookup" value

Kushal_Chawda — Tue, 02 Jun 2015 05:57:08 GMT

What if one Force Package will be there in Two PrCodes? Like in your Excel. W41 is there in two PrCodes for Mcode 111111

Re: Find Value in Text and "Vlookup" value

Tue, 02 Jun 2015 06:20:51 GMT

if you mean this, it should show the following:

Unique Number	PrCodes	Model Code & Package
CCCCC	1G1 1S1 1T3 3S1 3Y4 5D4 9JB B83 KA2 KP4 W41 W90	111111 - SE
DDDDD	1G1 1S1 1T3 3S1 3Y4 5D4 9JB B83 KA2 KP4 W41 W90	111111 - SE

Re: Find Value in Text and "Vlookup" value

Tue, 02 Jun 2015 10:25:37 GMT

So the biggest challenge is to find the "Forced Package" in the "PrCodes" ... that is what gives me the headache in the whole problem

Re: Find Value in Text and "Vlookup" value

ramoncova06 — Tue, 02 Jun 2015 19:50:52 GMT

this seems to work

Re: Find Value in Text and "Vlookup" value

sasiparupudi1 — Tue, 02 Jun 2015 19:59:39 GMT

You could do some thing like the following.. Basically, loop through both the tables and create a mapping table programmatically using the look up value.Hope this helps,

LOAD [Forced Package] as FP ,

Mcode,

[Model Code & Package] as MCP

FROM

C:\Packages.xls

(biff, embedded labels, table is [Sheet1$]);

LOAD [Unique Number] as UniqNumber,

PrCodes,

Mcode

FROM

C:\NADIN.xls

(biff, embedded labels);

FOR i=0 to NoOfRows('x')-1

LET vPrCodesMcode=trim(Peek('Mcode',$(i),'x'));

LET vPrCodes=Peek('PrCodes',$(i),'x');

LET vUniqNumber=Peek('UniqNumber',$(i),'x');

TRACE $(vMcode),$(vForcedPackage);

FOR j=0 to NoOfRows('y')-1

LET vForcedPackage=peek('FP',$(j),'y');

LET vMcode=trim(peek('Mcode',$(j),'y'));

//TRACE $(vPrCodesMcode) ;

//TRACE $(vMcode);

let vMCP="";

if (substringcount('$(vPrCodes)','$(vForcedPackage)')>0) and ('$(vPrCodesMcode)'='$(vMcode)') then

Let vMCP=peek('MCP',$(j),'y');

exit For;

ENDIF;

TRACE $(vMCP);

NEXT j;

Map:

Load

'$(vMCP)' As MCP ,

'$(vMcode)' as Mcode,

'$(vPrCodes)' as PRCodes,

'$(vForcedPackage)' as FP,

'$(vUniqNumber)' as vUniqNumber

AutoGenerate(1);

NEXT i;

drop Table x;

drop Table y;

Re: Find Value in Text and "Vlookup" value

Wed, 03 Jun 2015 04:44:01 GMT

Thanks for that!

It is working perfectly... now the problem is that the loop takes ages for a hugh amount of data... is there a why to shorten it ?