topic Re: Match string to substring from another table in QlikView

Match string to substring from another table

Thu, 15 Jan 2015 12:29:07 GMT

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

id	category
1111	a
2222	b
3333	c
4444	d
5555	e

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

Re: Match string to substring from another table

jerem1234 — Thu, 15 Jan 2015 17:43:45 GMT

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!

Re: Match string to substring from another table

Thu, 15 Jan 2015 18:14:43 GMT

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

Re: Match string to substring from another table

maxgro — Thu, 15 Jan 2015 19:15:57 GMT

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

Peter_Cammaert — Thu, 15 Jan 2015 20:13:54 GMT

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.

Re: Match string to substring from another table

NickHoff — Thu, 15 Jan 2015 20:17:23 GMT

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

MarcoWedel — Thu, 15 Jan 2015 22:52:58 GMT

Hi,

you could create an additional link table like this:

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

Re: Match string to substring from another table

anbu1984 — Sun, 18 Jan 2015 12:14:59 GMT

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;