Link 1 field to possible values in 2 fields?

[itatspectrum]

Hi All,

I have the following football data, and the results. But the team can exist in 2 fields, either the home or away team. How can we link or process the tables so that I can see all results for a team I select? I want to filter to Arsenal and see all their results.

Teams

Arsenal

Aston Villa

Bournemouth

Burnley

Chelsea

Crystal Palace

Everton

Hull

Results

Div	Date	HomeTeam	AwayTeam	FTHG	FTAG	FTR	HTHG
E0	13/08/2016	Burnley	Swansea	0	1	A	0
E0	13/08/2016	Crystal Palace	West Brom	0	1	A	0
E0	13/08/2016	Everton	Tottenham	1	1	D	1
E0	13/08/2016	Hull	Leicester	2	1	H	1
E0	13/08/2016	Man City	Sunderland	2	1	H	1
E0	13/08/2016	Middlesbrough	Stoke	1	1	D	1
E0	13/08/2016	Southampton	Watford	1	1	D	0
E0	14/08/2016	Arsenal	Liverpool	3	4	A	1
E0	14/08/2016	Bournemouth	Man United	1	3	A	0
E0	15/08/2016	Chelsea	West Ham	2	1	H	0
E0	19/08/2016	Man United	Southampton	2	0	H	1
E0	20/08/2016	Burnley	Liverpool	2	0	H	2
E0	20/08/2016	Leicester	Arsenal	0	0	D	0
E0	20/08/2016	Stoke	Man City	1	4	A	0
E0	20/08/2016	Swansea	Hull	0	2	A	0
E0	20/08/2016	Tottenham	Crystal Palace	1	0	H	0
E0	20/08/2016	Watford	Chelsea	1	2	A	0
E0	20/08/2016	West Brom	Everton	1	2	A	1
E0	21/08/2016	Sunderland	Middlesbrough	1	2	A	0

Cheers

Steve

[MarcoWedel]

Hi,

maybe like this:

tabResults:
LOAD RecNo() as ID, *
FROM [https://community.qlik.com/thread/247874] (html, codepage is 1252, embedded labels, table is @2);

tabTeams:
CrossTable (HomeAway, Team)
LOAD ID,
    HomeTeam,
    AwayTeam
Resident tabResults;

hope this helps

regards

Marco

[sunny_talwar]

May be using a link table concept...

Table:

LOAD RowNo() as UniqueIdentifier,

  *;

LOAD * INLINE [

    Div, Date, HomeTeam, AwayTeam, FTHG, FTAG, FTR, HTHG

    E0, 13/08/2016, Burnley, Swansea, 0, 1, A, 0

    E0, 13/08/2016, Crystal Palace, West Brom, 0, 1, A, 0

    E0, 13/08/2016, Everton, Tottenham, 1, 1, D, 1

    E0, 13/08/2016, Hull, Leicester, 2, 1, H, 1

    E0, 13/08/2016, Man City, Sunderland, 2, 1, H, 1

    E0, 13/08/2016, Middlesbrough, Stoke, 1, 1, D, 1

    E0, 13/08/2016, Southampton, Watford, 1, 1, D, 0

    E0, 14/08/2016, Arsenal, Liverpool, 3, 4, A, 1

    E0, 14/08/2016, Bournemouth, Man United, 1, 3, A, 0

    E0, 15/08/2016, Chelsea, West Ham, 2, 1, H, 0

    E0, 19/08/2016, Man United, Southampton, 2, 0, H, 1

    E0, 20/08/2016, Burnley, Liverpool, 2, 0, H, 2

    E0, 20/08/2016, Leicester, Arsenal, 0, 0, D, 0

    E0, 20/08/2016, Stoke, Man City, 1, 4, A, 0

    E0, 20/08/2016, Swansea, Hull, 0, 2, A, 0

    E0, 20/08/2016, Tottenham, Crystal Palace, 1, 0, H, 0

    E0, 20/08/2016, Watford, Chelsea, 1, 2, A, 0

    E0, 20/08/2016, West Brom, Everton, 1, 2, A, 1

    E0, 21/08/2016, Sunderland, Middlesbrough, 1, 2, A, 0

];

Team:

LOAD DISTINCT

  HomeTeam as Team,

  UniqueIdentifier

Resident Table;

Concatenate (Team)

LOAD Distinct

  AwayTeam as Team,

  UniqueIdentifier

Resident Table;