Merging two tables linked by a common field

[TweetingCynical]

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

[asinha1991]

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]

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]

[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 · Accepted solution]

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

Best Regards,

Angel Tomov