Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
TweetingCynical
Contributor
Contributor

Merging two tables linked by a common field

Hi All,

 

I'm relatively new to Qlik Sense and have what you all might think to be a fairly straight forward problem to fix.

I have two tables of data. One is web connection to an HTML source of information that I do not own or update. The other is a table I created to add information which is linked to the first table. The fields are as follows:

[Sweepstake Info]:
LOAD
[Team],
[Owner],
[2018/19 Pos],
[Handicap]
FROM [lib://AttachedFiles/Sweepstake Info.txt]
(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

[Premier League 2019/20]:
LOAD
[#],
[Team],
[Pl],
[W],
[D],
[L],
[F],
[A],
[GD],
[Pts],
[Last 6]
FROM [lib://EPL Table (qlikcloud_qlikid_j.gsmith)]
(html, codepage is 1252, embedded labels, table is [Premier League 2019/20]);

 

The tables are linked by the field [Team] in each table, but there is only one row of data for each [Team], so all I want to do, is create a third table which has all the fields of both tables (not duplicating the [Team] field of course), with another field created which is a calculation of [Handicap] in one table, added to the [Pts] of the other table, and given the new field name [Total Pts].

How might I go about this without disrupting the ongoing web connection that keeps one of those tables up to date?

Thanks in advance,

Jon

1 Solution

Accepted Solutions
tomovangel
Partner - Specialist
Partner - Specialist

I have added something to your script, can you try 🙂

 

Best Regards,

Angel Tomov

View solution in original post

4 Replies
asinha1991
Creator III
Creator III

something like this:

temptable:

LOAD
[Team],
[Owner],
[2018/19 Pos],
[Handicap]
FROM
[lib://AttachedFiles/Sweepstake Info.txt]
(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

outer join
LOAD
[#],
[Team],
[Pl],
[W],
[D],
[L],
[F],
[A],
[GD],
[Pts],
[Last 6]
FROM
[lib://EPL Table (qlikcloud_qlikid_j.gsmith)]
(html, codepage is 1252, embedded labels, table is [Premier League 2019/20]);

thirdtable:

Load *, Pts+Handicap as whatever Resident TempTable;

drop table temptable;

TweetingCynical
Contributor
Contributor
Author

Hi,

Thanks for your reply. I tried this and got an error saying Table 'temptable' not found. So I put the square brackets around them as was produced automatically by QlikSense for other tables. This still doesn't work, I still get Table 'TempTable' not found.

Any ideas?

Thanks, Jon. 

[TempTable]:
LOAD
[Team],
[Owner],
[2018/19 Pos],
[Handicap]
FROM [lib://AttachedFiles/Sweepstake Info.txt]
(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

outer join
LOAD
[#],
[Team],
[Pl],
[W],
[D],
[L],
[F],
[A],
[GD],
[Pts],
[Last 6]
FROM [lib://EPL Table (qlikcloud_qlikid_j.gsmith)]
(html, codepage is 1252, embedded labels, table is [Premier League 2019/20]);

[EPL Sweepstake Data]:

Load *, [Pts]+[Handicap] as [Total Pts] Resident TempTable;

drop table TempTable;
tomovangel
Partner - Specialist
Partner - Specialist

[TempTable]:
LOAD
[Team],
[Owner],
[2018/19 Pos],
[Handicap]
FROM [lib://AttachedFiles/Sweepstake Info.txt]
(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

outer join
LOAD
[#],
[Team],
[Pl],
[W],
[D],
[L],
[F],
[A],
[GD],
[Pts],
[Last 6]
FROM [lib://EPL Table (qlikcloud_qlikid_j.gsmith)]
(html, codepage is 1252, embedded labels, table is [Premier League 2019/20]);
NoConcatenate
[EPL Sweepstake Data]:

Load *, [Pts]+[Handicap] as [Total Pts] Resident TempTable;

drop table TempTable;
tomovangel
Partner - Specialist
Partner - Specialist

I have added something to your script, can you try 🙂

 

Best Regards,

Angel Tomov