Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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;
Thank you Gysbert
That does it - I'll try it my actual script with the full tables.
Mark