Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

20 Replies
Not applicable
Author

No one has an idea how to solve this?

ramoncova06
Specialist III
Specialist III

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

Not applicable
Author

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

Not applicable
Author

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

Kushal_Chawda

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

Not applicable
Author

    if you mean this, it should show the following:

Unique NumberPrCodesModel Code & Package
CCCCC1G1 1S1 1T3 3S1 3Y4 5D4 9JB B83 KA2 KP4 W41 W90111111 - SE
DDDDD1G1 1S1 1T3 3S1 3Y4 5D4 9JB B83 KA2 KP4 W41 W90111111 - SE
Not applicable
Author

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

ramoncova06
Specialist III
Specialist III

this seems to work

sasiparupudi1
Master III
Master III

Hi

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,

y:

LOAD [Forced Package] as FP ,

     Mcode,

     [Model Code & Package] as MCP

FROM

C:\Packages.xls

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

x:

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;

Not applicable
Author

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 ?