topic Re: search engine in script in QlikView

search engine in script

Tue, 23 Aug 2011 16:09:00 GMT

Hello,

I am new to this community and like to ask a question.

In our database we have a variable with an description of products. What we miss is a variable with Brandname.

In QV I can use the search option (loupe) to find the associating brands. But I cannot do this for multiple brands or at least I don't know how.

Is it possible to make a script in which I 'automatically' appoint a brand in a new variable 'Brand' from an other variable?

Thanks for your help.

Ronald

search engine in script

swuehl — Tue, 23 Aug 2011 19:06:07 GMT

Welcome, Ronald,

I am not sure if I fully understand your question. Could you post an example of product descriptions and how you want to generate brands out of that (or am I totally off?)?

I don't understand how you can search for brands with the search function in the front end if you don't have the information in the tables. Or are brand names part of the description?

If we understand how you search for brands with the search function, it should/might be possible to generate a new field in the script, too.

Regards,

Stefan

Re: search engine in script

Wed, 24 Aug 2011 09:42:23 GMT

Dear Stefan,

In our database we have the product description in the variable ‘art_artikel_oms

Here are some examples in our database and how we like to rearrange into brands.

You notice that e.g. BOSS as well as H.Boss as well as Hugo Boss as well as Hugo must direct to Hugo Boss

And both C.Klein as well as CK must direct to Calvin Klein,

But HAJENIUS KLEINE TUITKNAP with ‘KLEIN’ in description is NOT Calvin Klein

ART_ARTIKEL_OMS

Brand

remark

BOSSBL M belt, 85, dk brown

Hugo Boss

BOSSOL M t-shirt, L, Green

Hugo Boss

Bvlgari Man Edtsp 100

Bvlgari

C.Klein CK One Edtsp 200

Calvin Klein

C.Klein Eternity Men Ash 100

Calvin Klein

Chanel Vitalumiere Aqua Spf15 Found BR20

Chanel

CK M bw bottom, L, Black

Calvin Klein

D&G The One Gentleman Ash 100

D&G

D.Karan DKNY Women Edpsp 50

Donna Karan DKNY

Davidoff Champion Deost 70

Davidoff

DAVIDOFF EXQUISITOS 10 ST

Davidoff

Dior Miss Dior Cherie Edpsp 50

Dior

E.Lauder Pure Color Lipgl 03

Estee Lauder

G.Armani Code Women Edpsp 50

Giorgio Armani

Gucci Guilty Edtsp 50

Gucci

H.Boss Boss Bottled Night Ash 50

Hugo Boss

HAJENIUS KLEINE TUITKNAK

Hajenius

'=cigar, no calvin klein

Hugo Boss Femme by Boss Edpsp 50

Boss

Hugo Element Deost 75

Boss

J WALKER BLACK LABEL 40%

Johnny Walker

Kenzo Flower Women Parsp 50

Kenzo

KLEINBRILJANT

'=diamond, no calvin klein

Lancome Miracle Edpsp 50

Lancome

L'Oréal Resist&Shine Titanium Nailp 501

L'Oreal

O'Neill Moya pink 39

O' Neill

Prada LR Metal Brn PS162 PS50M 4AC 6S1

Prada

SOUTH CHARDONNAY ORGANIC

'=wine, no Donna Karan

YSL La Nuit Parsp 100

Yves Saint Laurent

Can you help me?

Best regards,

Ronald

Van: swuehl

Verzonden: dinsdag 23 augustus 2011 21:06

Aan: Wolfers, Ronald

Onderwerp: - Re: search engine in script

QlikCommunity<http://community.qlik.com/index.jspa>

Re: search engine in script

created by swuehl<http://community.qlik.com/people/swuehl> in New to QlikView - View the full discussion<http://community.qlik.com/message/143375#143375

Re: search engine in script

swuehl — Wed, 24 Aug 2011 15:07:26 GMT

Ronald,

I will try:

I had a look at your data and noticed that at the beginning of each description ART_ARTIKEL_OMS, there we find the brand identification tag, is this correct?

My approach has four steps:

1) Build a table with a matching relation between the identification tag and the brand name (I think we can't live without something like that), like

...

BOSS, Hugo Boss

...

to match both BOSSOL and BOSSBL and link to Hugo Boss.

2) Sort this table by descending Tag length

3) Read in a ART_ARTIKEL_OMS and for each read record, loop though all matching tags and compare the left part of ART_ARTIKEL_OMS with the matching tag, if these match, we have found a brand. break this loop if all tags are compared or we have found a brand and continue with next ART_ARTIKEL_OMS

(Here comes the sorting of the tags into play: If you would have two tags like BOSS and BOSSOL, I would like to compare first the more significant (i.e. longer) tag to the ART_ARTIKEL_OMS, if these match, remember we break the loop).

4) Now we have to remove all the rows with no match, load the table again with an where clause.

Maybe I missed something more simple, but that's how it should work assuming your ART_ARTIKEL_OMS start with a brand identification. If they don't always do, we might could easily change the comparision to a match() function or similar, but we could run into deep trouble if there are matches that don't indicate a brand (like in some of your example rows (with Donna, Klein tags))

See attached sample for more details.

Hope this helps,

Stefan

Re: search engine in script

Thu, 25 Aug 2011 15:05:15 GMT

Dear Stefan,

Thx for your help, we gonna try if this works.

Rgds

Ronald

Van: swuehl

Verzonden: woensdag 24 augustus 2011 17:08

Aan: Wolfers, Ronald

Onderwerp: - Re: search engine in script

QlikCommunity<http://community.qlik.com/index.jspa>

Re: search engine in script

created by swuehl<http://community.qlik.com/people/swuehl> in New to QlikView - View the full discussion<http://community.qlik.com/message/143769#143769

search engine in script

swuehl — Fri, 09 Sep 2011 09:58:10 GMT

Hi Ronald,

I am just curious...

Have you got it working?

Regards,

Stefan