topic Creating a new table using existing resident tables in QlikView

Creating a new table using existing resident tables

mla3eruva — Tue, 05 Jan 2016 13:27:27 GMT

I need to create a new table in my model that unions two dimension tables. In our fact table, we have two different attributes (Project# and an Award#) on each row - each of two attributes can be "owned" by the same or different organizations. I can easily select all the Projects owned by an Org OR all the Awards owned by an Org. I need a new field that allows me to select all the rows where an Org owns either/both of the Awards and Projects.

I'm new to Qlik and think I'm missing something pretty basic in how to create this bridge table but i can't figure out what the load script needs to be.

My starting data looks like this:

Fact Table
Project ID	Award ID	Project-Award Key	Amount
ABC	123	ABC-123	100
DEF	123	DEF-123	100
GHI	345	GHI-345	100
JKL	678	JKL-678	100

Project Dim
Project ID	Project Org
ABC	Sales
DEF	Marketing
GHI	Finance
JKL	Operations

Award Dim
Award ID	Award Org
123	Sales
123	Sales
345	Operations
678	Finance

My new table should look like this:

Project-Award Key	Org
ABC-123	Sales
DEF-123	Marketing
GHI-345	Finance
JKL-678	Operations
DEF-123	Sales
GHI-345	Operations
JKL-678	Finance

I've attached a QVW with some sample data below.

Thank you in advance.

Re: Creating a new table using existing resident tables

Gysbert_Wassenaar — Wed, 06 Jan 2016 19:00:17 GMT

Try this:

Fact:

LOAD * Inline [

Project_ID,Award_ID,Project_Award_Key,Amount

ABC,123,ABC-123,100

DEF,123,DEF-123,100

GHI,345,GHI-345,100

JKL,678,JKL-678,100

];

PA:

LOAD * Inline [

Project_ID,Project_Org

ABC,Sales

DEF,Marketing

GHI,Finance

JKL,Operations

];

AW:

LOAD * Inline [

Award_ID,Award_Org

123,Sales

345,Operations

678,Finance

];

mapPA:

mapping load * Resident PA;

mapAW:

mapping load * Resident AW;

Result:

LOAD distinct Project_Award_Key, applymap('mapPA',Project_ID) as Org Resident Fact;

Concatenate(Result)

LOAD Project_Award_Key, applymap('mapAW',Award_ID) as Org Resident Fact;

Re: Creating a new table using existing resident tables

mla3eruva — Wed, 06 Jan 2016 20:28:06 GMT

Thank you Gysbert

That does it - I'll try it my actual script with the full tables.

Mark