Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chuckberman1
Contributor
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
Specialist
Specialist

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

View solution in original post

4 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor
Contributor
Author

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
Specialist
Specialist

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
Contributor
Contributor
Author

What a great trick!  Thank you so much!