Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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!
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
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;
What a great trick! Thank you so much!