Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Match string to substring from another table

Hi everyone,

Imagine two simple tables - one with IDs and the other with urls. Each url contains one ID, the ID position in the url is not fixed, and urls might also contain other non-ID numbers as the last example in Table2. Simple Left(), SubField(), KeepChar() won't work.

Table1

idcategory
1111a
2222b
3333c
4444d
5555e

Table2

urls
http://www.ipsum.com/1111/lorem
http://www.ipsum.com/lorem/2222
http://www.lorem.com/3333/lorem
http://www.lorem.com/ipsum/lorem/4444

/231/lorem/5555/lorem

I tried to extract the IDs from the urls with If(IsNum(url_sub) and Len(url_sub) = 4, url_sub, Null()) which somehow works as an expression but not in the script. Probably Mapping and MapSubString() is the best option but I couldn't make it work either. Do you have any ideas?

Thanks,

Ivelin

1 Solution

Accepted Solutions

Re: Match string to substring from another table

Hi,

you could create an additional link table like this:

QlikCommunity_Thread_148524_Pic1.JPG

QlikCommunity_Thread_148524_Pic2.JPG

Table1:

LOAD *

FROM [http://community.qlik.com/thread/148524] (html, codepage is 1252, embedded labels, table is @1);

Table2:

LOAD *,

    AutoNumberHash128(urls) as %URLid

FROM [http://community.qlik.com/thread/148524] (html, codepage is 1252, embedded labels, table is @2);

tabLink:

LOAD %URLid,

    id2 as id

Where Exists (id, id2); 

LOAD %URLid,

    SubField(urls, '/') as id2

Resident Table2;

hope this helps

regards

Marco

7 Replies
jerem1234
Valued Contributor II

Re: Match string to substring from another table

Hi Ivelin,

    I used this script. I wasn't sure the final data model you were looking for so i guessed. Hopefully the script and the attached dashboard will help. THe script was:

ids:

LOAD * INLINE

[

    id, category, visits

    1111, a, 100

    2222, b, 200

    3333, c, 300

    4444, d, 400

    5555, e, 500

  6666, f, 600

];

urls:

LOAD * INLINE

[

    url, pages

    http://www.ipsum.com/1111/lorem, 10

    http://www.ipsum.com/lorem/2222, 20

    http://www.lorem.com/3333/lorem, 30

    http://www.lorem.com/ipsum/lorem/4444, 40

    /lorem/5555/lorem, 50

  /12/ipsum/6666, 60

];

Left Join(ids)

Test:

Load url, if(isnum([Sub-url]) and len(trim([Sub-url]))=4, [Sub-url]) as id, pages

where isnum([Sub-url]) and len(trim([Sub-url]))=4;

Load url,

subfield(url, '/') as [Sub-url],

pages

Resident urls;

Drop Table urls;

Hope this helps!

Not applicable

Re: Match string to substring from another table

I didn't get your requirement. what is the expected output.

MVP
MVP

Re: Match string to substring from another table

1.png



ids:

LOAD * INLINE

[

    id, category, visits

    1111, a, 100

    2222, b, 200

    3333, c, 300

    4444, d, 400

    5555, e, 500

  6666, f, 600

];

//urls:

left join (ids)

LOAD rowno() as row, url, pages, SubField(url, '/') as id INLINE

[

    url, pages

    http://www.ipsum.com/1111/lorem, 10

    http://www.ipsum.com/lorem/2222, 20

    http://www.lorem.com/3333/lorem, 30

    http://www.lorem.com/ipsum/lorem/4444, 40

    /lorem/5555/lorem, 50

  /12/ipsum/6666, 60

];

Re: Match string to substring from another table

One minor improvement. This part will also work:

:

LEFT JOIN(ids)

LOAD url, [Sub-url] AS id, pages

WHERE isnum([Sub-url]) AND len(trim([Sub-url]))=4;

LOAD url,

subfield(url, '/') AS [Sub-url],

pages

RESIDENT urls;

:

No need to test&transform the sub-url if the where clause already filters out everything else.

qvelhoff
Valued Contributor

Re: Match string to substring from another table

will the URL ever have a numeric value in it aside from the ID?  If that is the case you could just use

KEEPCHAR(URL,1234567890) AS ID

Edit cancel this I just re-read the last option where it stated you may have an numeric other than the ID.

Re: Match string to substring from another table

Hi,

you could create an additional link table like this:

QlikCommunity_Thread_148524_Pic1.JPG

QlikCommunity_Thread_148524_Pic2.JPG

Table1:

LOAD *

FROM [http://community.qlik.com/thread/148524] (html, codepage is 1252, embedded labels, table is @1);

Table2:

LOAD *,

    AutoNumberHash128(urls) as %URLid

FROM [http://community.qlik.com/thread/148524] (html, codepage is 1252, embedded labels, table is @2);

tabLink:

LOAD %URLid,

    id2 as id

Where Exists (id, id2); 

LOAD %URLid,

    SubField(urls, '/') as id2

Resident Table2;

hope this helps

regards

Marco

anbu1984
Honored Contributor III

Re: Match string to substring from another table

ids:

LOAD * INLINE

[

    id, category, visits

    1111, a, 100

    2222, b, 200

    3333, c, 300

    4444, d, 400

    5555, e, 500

  6666, f, 600

];

//urls:

left join (ids)

LOAD * INLINE

[

    url, pages

    http://www.ipsum.com/1111/lorem, 10

    http://www.ipsum.com/lorem/2222, 20

    http://www.lorem.com/3333/lorem, 30

    http://www.lorem.com/ipsum/lorem/4444, 40

    /lorem/5555/lorem, 50

  /12/ipsum/6666, 60

];

NoConcatenate

Load * Resident ids Where Index(url,id);

Drop Table ids;

Community Browser