Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Ashish_1102
Contributor II
Contributor II

self joining a table using mixmatch

Dear community

ive a table like this -

Tab A :

Id Titledate
1Weekly1/1/2019
2weekly1/2/2019
3WeEkLy1/3/2019

 

From this i want to derive another table like this

Tab_Ext:

Title _Title _date
WeeklyWeekly1/1/2019
Weeklyweekly1/2/2019
WeeklyWeEkLy1/3/2019
weeklyWeekly1/1/2019
weeklyweekly1/2/2019
weeklyWeEkLy1/3/2019
WeEkLyWeekly1/1/2019
WeEkLyweekly1/2/2019
WeEkLyWeEkLy1/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'.

Labels (1)
  • MAT

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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;	

Capture.PNG

View solution in original post

7 Replies
Sergey_Shuklin
Specialist
Specialist

Hello!

Use JOIN to achive this.

week_join_pic1.png

Ashish_1102
Contributor II
Contributor II
Author

Hi Thanks For that.

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

Let's say i've something like this -

Id Titledate
1Weekly1/1/2019
2weekly1/2/2019
3xyz1/3/2019

 

Than My output will look like -

Title _Title _date
WeeklyWeekly1/1/2019
Weeklyweekly1/2/2019
weeklyWeekly1/1/2019
weeklyweekly1/2/2019
xyxxyz1/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
MVP
MVP

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;	

Capture.PNG

View solution in original post

Ashish_1102
Contributor II
Contributor II
Author

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
MVP
MVP

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

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
MVP
MVP

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?