Discussion Board for collaboration related to QlikView App Development.
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:
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?
Go to Solution.
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;
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.
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.
What a great trick! Thank you so much!