Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

chuckberman1
New Contributor

2 Tables resulting in a Synthetic Key

Question: I have a scaled down test case of 3 spreadsheets loaded as 2 tables.

My State table contains the State Abbreviation, State Name and Capital.

My two Employee tables are concatenated into 1 table that contains First Name, Last Name, Capital and State.

In some cases, the State is known, but the capital is not. In other cases, the Capital is known, but the State is not:

States:

State Employee:

Capital Employee:

  Script:  

Can anyone let me know how to modify my script so the synthetic key goes away and the table has just 16 rows of data that include all 5 fields?

1 Solution

Accepted Solutions
el_aprendiz111
Valued Contributor

Re: 2 Tables resulting in a Synthetic Key

Hi Chuck

LET vNull = 'No data';
[States]:
LOAD * Inline [
Abbrev,State,Capital
AL,Alabam,Montgomery
AK,Alaska,Juneau
AZ,Arizona,Phoenix
AR,Arkansas,Little Rock
CA,California,Sacramento
CO,Colorado,Denver
CT,Connecticut,Hartford
DE,Delaware,Dover
FL,Florida,Tallahassee
GA,Georgia,Atlanta
HI,Hawaii,Honolulu
ID,Idaho,Boise
]
;

Mapping  [sta]: LOAD State,Capital & ';' & Abbrev AS cap Resident States;
Mapping  [sta]: LOAD Capital, State & ';' & Abbrev AS cap Resident States;


[State Employe]:
LOAD *,
SubField(ApplyMap('sta',State, '$(vNull)'),';',1) AS Capital,
SubField(ApplyMap('sta',State, '$(vNull)'),';',2) AS Abbrev;
LOAD * Inline [
First,Last,State
Steve,Cook,California
Jake,Butler,Colorado
Ellen,Adams,Connecticut
Sam,Carter,Delaware
Kate,Jones,Florida
Melissa,Stewart,Georgia
Gus,Edwards,Hawaii
Ed,Black,Idaho

]
;




[Capital Employe]:
LOAD *,
SubField(ApplyMap('sta',Capital, '$(vNull)'),';',1) AS State,
SubField(ApplyMap('sta',Capital, '$(vNull)'),';',2) AS Abbrev;
LOAD * Inline [
First,Last,Capital
Fred,Jones,Montgomery
Joe,Smith,Juneau
Dennis,Adams,Phoenix
Mary,White,Little Rock
Susan,Johnson,Sacramento
Jerry,James,Denver
Mike,Riley,Hartford
Jennifer,Green,Dover
]
;

DROP Table States;
EXIT Script;

state.png

4 Replies
MVP & Luminary
MVP & Luminary

Re: 2 Tables resulting in a Synthetic Key

Hi Chuck,

here is what I'd suggest:

1. Load States into a temporary table.

2. Load State Employees into another temporary table.

3. Join from States into State Employees - the join will be performed by State.

4. Load Captial Employees into another temporary table

5. Join from States into Capital Employees - the join will be performed by Capital.

6. Concatenate State Employees and Capital Employees into a single table

7. Drop all temporary tables.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - this October in Boston!

chuckberman1
New Contributor

Re: 2 Tables resulting in a Synthetic Key

This is very helpful Oleg, but could you provide the syntax for performing those instructions? I’ve been trying inner joins, left joins, followed by noconcatenate loads and concatenate loads, and I can’t seem to get it right.

Thanks,

-Chuck

el_aprendiz111
Valued Contributor

Re: 2 Tables resulting in a Synthetic Key

Hi Chuck

LET vNull = 'No data';
[States]:
LOAD * Inline [
Abbrev,State,Capital
AL,Alabam,Montgomery
AK,Alaska,Juneau
AZ,Arizona,Phoenix
AR,Arkansas,Little Rock
CA,California,Sacramento
CO,Colorado,Denver
CT,Connecticut,Hartford
DE,Delaware,Dover
FL,Florida,Tallahassee
GA,Georgia,Atlanta
HI,Hawaii,Honolulu
ID,Idaho,Boise
]
;

Mapping  [sta]: LOAD State,Capital & ';' & Abbrev AS cap Resident States;
Mapping  [sta]: LOAD Capital, State & ';' & Abbrev AS cap Resident States;


[State Employe]:
LOAD *,
SubField(ApplyMap('sta',State, '$(vNull)'),';',1) AS Capital,
SubField(ApplyMap('sta',State, '$(vNull)'),';',2) AS Abbrev;
LOAD * Inline [
First,Last,State
Steve,Cook,California
Jake,Butler,Colorado
Ellen,Adams,Connecticut
Sam,Carter,Delaware
Kate,Jones,Florida
Melissa,Stewart,Georgia
Gus,Edwards,Hawaii
Ed,Black,Idaho

]
;




[Capital Employe]:
LOAD *,
SubField(ApplyMap('sta',Capital, '$(vNull)'),';',1) AS State,
SubField(ApplyMap('sta',Capital, '$(vNull)'),';',2) AS Abbrev;
LOAD * Inline [
First,Last,Capital
Fred,Jones,Montgomery
Joe,Smith,Juneau
Dennis,Adams,Phoenix
Mary,White,Little Rock
Susan,Johnson,Sacramento
Jerry,James,Denver
Mike,Riley,Hartford
Jennifer,Green,Dover
]
;

DROP Table States;
EXIT Script;

state.png

chuckberman1
New Contributor

Re: 2 Tables resulting in a Synthetic Key

What a great trick!  Thank you so much!