One to Many Relationship or multiple common fields

[#1]

Hello,

I have two tables; Data and Entities

Data tables

Customer	CountryID	ContinentID	StateID
ABC Inc	54321	12345	98765
NYPD	23456	65432	87654
America Corp	54321	12345	98765
USA Inc	23456	87654	65432

Entities

EntityID	CountryName	ContinentName	StateName
54321	USA
23456	Europe
12345		NorthAmerica
65432		SouthAmerica
98765			New York
87654			Texas

The Entities table is used to filter the Data table.  I need to create a relationship between the following:

CountryID - EntityID

ContinentID - EntityID

StateID - EntityID

Is there anyway to link multiple fields in one table to a single field in another table?

Your assistance is greatly appreciated.

Regards,

Stephanie Smith

[Jason_Michaelides · Accepted solution]

Hi Stephanie,

You will first need to transform the top table into a tall thin version. CrossTable() will do this for you. Try:

Data:

CrossTable(EntityType,EntityID)

LOAD

  *

FROM Data...;

Entities:

LOAD

  *

FROM Entities...;

The two tables will then link on EntityID.

Hope this helps,

Jason

[#3]

Hello Jason,

Thanks for the reply.

I currently do not have a field called 'EntityType'.  Am I renaming my fields my EntityType?

I'm not 100% sure how cross tables work, hence my question.

Thanks,

Stephanie Smith

[Jason_Michaelides]

You can read up on CrossTable() in the reference manual.  Essentially, it transforms the data from many columns to 2 columns of name-value pairs.  So, in your case, your data will look like this:

Customer	EntityType	EntityID
ABC Inc	CountryID	54321
NYPD	CountryID	23456
America Corp	CountryID	54321
USA Inc	CountryID	23456
ABC Inc	ContinentID	12345
NYPD	ContinentID	65432
America Corp	ContinentID	12345
USA Inc	ContinentID	87654
ABC Inc	StateID	98765
NYPD	StateID	87654
America Corp	StateID	98765
USA Inc	StateID	65432

Hope this helps,

Jason

[swuehl]

You probably don't need your Entities and IDs except for the lookup. So you can do that lookup / mapping in the load, and keeping only the necessary data, like

CountryMap:

Mapping LOAD EntityID,

     CountryName

//     ContinentName,

//     StateName

FROM

[http://community.qlik.com/thread/59606?tstart=0]

(html, codepage is 1252, embedded labels, table is @2)

where len(trim(CountryName));

ContinentMap:

Mapping LOAD EntityID,

//     CountryName

     ContinentName

//     StateName

FROM

[http://community.qlik.com/thread/59606?tstart=0]

(html, codepage is 1252, embedded labels, table is @2)

where len(trim(ContinentName));

StateMap:

Mapping LOAD EntityID,

//     CountryName

//     ContinentName

     StateName

FROM

[http://community.qlik.com/thread/59606?tstart=0]

(html, codepage is 1252, embedded labels, table is @2)

where len(trim(StateName));

Data:

LOAD Customer,

     applymap('CountryMap',CountryID,'unknown') as CountryName,

     applymap('ContinentMap',ContinentID,'unknown') as ContinentName,

     applymap('StateMap',StateID,'unknown') as StateName

FROM

[http://community.qlik.com/thread/59606?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

Regards,

Stefan

edit: Missed a map on upload

[#6]

Hello Jason,

The Cross Table worked perfectly.

Thanks a million

Stephanie Smith