topic Re: Join Tables in QlikView

Join Tables

evansabres — Fri, 01 Jun 2018 00:35:38 GMT

I have a table called 'POSTS' with several different fields, including 'link'. I have another table called 'VIDEO' with several fields, including one called 'videolink'. The two fields I mentioned are the same, just named differently. What i want to do is join the VIDEO table to the POSTS table, keeping all fields from the POSTS table, but only retain a few fields from the VIDEO table. How would this be done in the script.

Re: Join Tables

Anonymous — Fri, 01 Jun 2018 07:06:04 GMT

Hye,

perhaps something like this

tmp:

Load link as videolink, Var1, var2 ...;

select link, var1,var2 .... from POSTS;

Left join (tmp)

load videolink, listofvar

resident VIDEO;

Re: Join Tables

evansabres — Fri, 01 Jun 2018 14:13:30 GMT

Sorry, I do not follow, I may not be providing enough information either. My desired end result is one table where all posts show data from both of these tables, where applicable (Not all posts in the Twitter Table will be in the TwitterVideo table, so those that do no appear would be null

Here is my script currently:

Twitter:

LOAD

[Tweet id] AS TWid,

[Tweet permalink] AS TWlink, --This is the common field that I want to join on

[Tweet text] AS TWpost,

time As TWdate,

impressions AS TWimp,

engagements AS TWeng,

[engagement rate] AS lTWengrt,

retweets AS TWrt,

replies AS TWreplies,

likes AS TWlikes,

[url clicks] AS TWlinkclicks,

[detail expands] AS TWdetailexpands,

[media views] AS TWmediaviews,

[media engagements] AS lTWmediaeng

FROM Local Data Source;

TwitterVideo:

LOAD

duration AS TWvideolength,

[playback 25] AS TWvideo25perc,

[playback 50] AS TWvideo50perc,

[playback 75] AS TWvideo75perc,

[playback completes] AS TWvideocomplete,

[video views] AS TWvideoviews,

[completion rate] AS TWvideo,

[topTweet_1 text] AS lTWvideopost,

[topTweet_1 permalink] AS TWlink -- This is the common field that I want to link on.

FROM [local data source]

Re: Join Tables

Anonymous — Fri, 01 Jun 2018 14:40:29 GMT

something like this :

MYData1:

Load * INLINE [

MyKey, Var1, Var2, Var3

A,1,1,1

B,1,2,3

C,4,5,6

E,1,2,3

];

MyData2:

Load * INLINE [

MyKey, Var4, Var5, Var6

A,1,1,1

C,1,2,3

E,4,5,6

F,1,2,3

];

join (MYData1)

Load MyKey , Var4, Var5, Var6

resident MyData2;

like a full join

Re: Join Tables

evansabres — Fri, 01 Jun 2018 14:42:45 GMT

Does it make a difference that these are not inline tables? Both of these tables are coming from different .csv files

Re: Join Tables

Anonymous — Fri, 01 Jun 2018 15:18:38 GMT

no, it's the same.