topic self joining a table using mixmatch in QlikView

self joining a table using mixmatch

Ashish_1102 — Sat, 16 Nov 2024 20:42:34 GMT

Dear community

ive a table like this -

Tab A :

Id	Title	date
1	Weekly	1/1/2019
2	weekly	1/2/2019
3	WeEkLy	1/3/2019

From this i want to derive another table like this

Tab_Ext:

Title	_Title	_date
Weekly	Weekly	1/1/2019
Weekly	weekly	1/2/2019
Weekly	WeEkLy	1/3/2019
weekly	Weekly	1/1/2019
weekly	weekly	1/2/2019
weekly	WeEkLy	1/3/2019
WeEkLy	Weekly	1/1/2019
WeEkLy	weekly	1/2/2019
WeEkLy	WeEkLy	1/3/2019

so here i basically want to mix-match titles and wherever there's a match, i want to create an association.

So finally all those titles which match are stored in '_Title' and their respective dates is stored in '_Date'.

Re: self joining a table using mixmatch

Sergey_Shuklin — Fri, 07 Jun 2019 11:21:30 GMT

Hello!

Use JOIN to achive this.

Re: self joining a table using mixmatch

Ashish_1102 — Fri, 07 Jun 2019 11:50:35 GMT

Hi Thanks For that.

But this is not what i'm looking for..

Let's say i've something like this -

Id	Title	date
1	Weekly	1/1/2019
2	weekly	1/2/2019
3	xyz	1/3/2019

Than My output will look like -

Title	_Title	_date
Weekly	Weekly	1/1/2019
Weekly	weekly	1/2/2019
weekly	Weekly	1/1/2019
weekly	weekly	1/2/2019
xyx	xyz	1/3/2019

The idea is to populate those values in _Title field where there is case insensitive match with title.

hope that makes sense

Re: self joining a table using mixmatch

tresB — Fri, 07 Jun 2019 12:12:34 GMT

This?

T1: Load date, Title, Upper(Title) as Key Inline [ Id Title date 1 Weekly 1/1/2019 2 weekly 1/2/2019 3 xyz 1/3/2019 ] (delimiter is spaces); Join T2: LOAD Title as _Title, Upper(Title) as Key Resident T1;

Re: self joining a table using mixmatch

Ashish_1102 — Fri, 07 Jun 2019 12:40:46 GMT

Thanks a lot. very very helpful.

i need another small tweak.

if in column Title, i've a value like this - 'The Weekly', then it needs to be associated as well.

I mean if title wildmatches any other value in it, then that goes to the '_title' column too.

please could you suggest a way

Re: self joining a table using mixmatch

tresB — Fri, 07 Jun 2019 12:49:20 GMT

Is the search going to be always on 'weekly' ? or, there could other key values as well?

Re: self joining a table using mixmatch

Ashish_1102 — Fri, 07 Jun 2019 13:01:19 GMT

There could be many other values as well..

E.g if i have 4 values under 'Title' -

xyz, abc xyz, abc xyz def, XYZ.

all of them should be associated and should be present in '_Title' column.

This would give me 16 rows of data.

Re: self joining a table using mixmatch

tresB — Fri, 07 Jun 2019 13:17:29 GMT

But how do you know that 'xyz' is the value which need to look for? why not 'abc' or any other value for that matter?