Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

One to Many Relationship or multiple common fields

Hello,

I have two tables; Data and Entities

Data tables

CustomerCountryIDContinentIDStateID
ABC Inc543211234598765
NYPD234566543287654
America Corp543211234598765
USA Inc234568765465432

Entities

EntityIDCountryNameContinentNameStateName
54321USA
23456Europe
12345NorthAmerica
65432SouthAmerica
98765New York
87654Texas

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

1 Solution

Accepted Solutions
jason_michaelid
Honored Contributor II

Re: One to Many Relationship or multiple common fields

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

5 Replies
jason_michaelid
Honored Contributor II

Re: One to Many Relationship or multiple common fields

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

Not applicable

Re: One to Many Relationship or multiple common fields

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_michaelid
Honored Contributor II

Re: One to Many Relationship or multiple common fields

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:

CustomerEntityTypeEntityID
ABC IncCountryID54321
NYPDCountryID23456
America CorpCountryID54321
USA IncCountryID23456
ABC IncContinentID12345
NYPDContinentID65432
America CorpContinentID12345
USA IncContinentID87654
ABC IncStateID98765
NYPDStateID87654
America CorpStateID98765
USA IncStateID65432

Hope this helps,

Jason

MVP
MVP

Re: One to Many Relationship or multiple common fields

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

Not applicable

Re: One to Many Relationship or multiple common fields

Hello Jason,

The Cross Table worked perfectly.

Thanks a million

Stephanie Smith