self joining a table using mixmatch

[Ashish_1102]

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'.

[Sergey_Shuklin]

Hello!

Use JOIN to achive this.

[Ashish_1102]

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

[tresesco · Accepted solution]

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;	

[Ashish_1102]

Hi

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

[tresesco]

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

[Ashish_1102]

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.

[tresesco]

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?