Match string to substring from another table

[#1]

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

[jerem1234]

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!

[#3]

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

[maxgro]

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

];

[Peter_Cammaert]

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.

[NickHoff]

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.

[MarcoWedel · Accepted solution]

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

[anbu1984]

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;