Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community Folks, I got requirement context based (either one field or more than field) association and please find the below an example.
I have two tables fact table and country dimension like below:
Fact Table:
OrderID | OrderContextKey | OrderContextValue | Amount |
1 | Country | USA | 116 |
2 | Country-State | USA-NY | 56 |
3 | State-City | NY-Albany | 36 |
4 | State-City | NY-New York | 100 |
5 | State-City | CA-Freemont | 70 |
6 | State-City | NJ-Newark | 77 |
7 | State-City | NY-Buffalo | 108 |
8 | Country-State | USA-PA | 78 |
9 | Country-State-City | USA-CA-San Diego | 100 |
Country Dimension Table:
Country | State | City | Client |
USA | NY | Buffalo | A |
USA | CA | Freemont | B |
USA | NY | Albany | C |
USA | NJ | Newark | D |
USA | PA | Allen Town | E |
USA | NY | New York | F |
USA | CA | Los Angels | G |
USA | CA | San Diego | H |
There is no direct key between these tables but OrderContextKey in the fact table need to use create association.
So I write script, Loop based on the OrderContextKey and generate the same key on the Country dimension table.
Fact:
LOAD OrderID,
OrderContextKey,
OrderContextValue,
OrderContextKey & '|' & OrderContextValue AS LinkFackKey,
Amount
FROM ContextAssociation.xlsx (ooxml, embedded labels, table is Fact);
ContextDIM:
LOAD
RowNo() AS LinkDimKey,
Country,
State,
City,
Client
FROM ContextAssociation.xlsx (ooxml, embedded labels, table is CountryDim);
For i=1 to FieldValueCount('OrderContextKey')
LET vOrderContextKey = FieldValue('OrderContextKey',$(i)) ;
LET vDimLoadKey = Replace('$(vOrderContextKey)','-',Replace(' & |-| & ','|',Chr(39))) ;
TRACE Order Context Key => $(vOrderContextKey);
TRACE Dimension Load Key => $(vDimLoadKey);
TempLinkTable:
LOAD
OrderContextKey,
OrderContextValue
Resident Fact
Where OrderContextKey = '$(vOrderContextKey)';
Inner Join(TempLinkTable)
LOAD
LinkDimKey,
'$(vOrderContextKey)' AS OrderContextKey,
$(vDimLoadKey) AS OrderContextValue
Resident ContextDIM;
LinkTable:
LOAD
LinkDimKey,
OrderContextKey & '|' & OrderContextValue AS LinkFackKey
Resident TempLinkTable;
DROP Table TempLinkTable;
SET vOrderContextKey = ;
SET vDimLoadKey = ;
NEXT i
SET i = ;
The above script works fine but my tables very huge. Dimension table had 3.5M records and Fact table have 10M records. Also, OrderContextKey had almost 35+ distinct combinations.
Is there any best approach other than above approach to solve association issue.
Thanks in Advance.
Rob, do you have any input rwunderlich ?
I'm not sure if I'm completely understanding what the expected outcome is, but I took a stab at it.
Fact:
LOAD * INLINE [
OrderID, OrderContextKey, OrderContextValue, Amount
1, Country, USA, 116
2, Country-State, USA-NY, 56
3, State-City, NY-Albany, 36
4, State-City, NY-New York, 100
5, State-City, CA-Freemont, 70
6, State-City, NJ-Newark, 77
7, State-City, NY-Buffalo, 108
8, Country-State, USA-PA, 78
9, Country-State-City, USA-CA-San Diego, 100
];
Dimension:
LOAD * INLINE [
Country, State, City, Client
USA, NY, Buffalo, A
USA, CA, Freemont, B
USA, NY, Albany, C
USA, NJ, Newark, D
USA, PA, Allen Town, E
USA, NY, New York, F
USA, CA, Los Angels, G
USA, CA, San Diego, H
];
/* Join each combination of OrderContextKey to Dimension in order to link */
Key_Combinations_Temp:
LOAD Country, State, City, Client, Country AS OrderContextValue
RESIDENT Dimension;
CONCATENATE (Key_Combinations_Temp)
LOAD Country, State, City, Client, Country & '-' & State AS OrderContextValue
RESIDENT Dimension;
CONCATENATE (Key_Combinations_Temp)
LOAD Country, State, City, Client, Country & '-' & State & '-' & City AS OrderContextValue
RESIDENT Dimension;
CONCATENATE (Key_Combinations_Temp)
LOAD Country, State, City, Client, State & '-' & City AS OrderContextValue
RESIDENT Dimension;
LEFT JOIN (Dimension)
LOAD *
RESIDENT Key_Combinations_Temp;
DROP TABLE Key_Combinations_Temp;
I've also attached a sample file.
Nicole, I tried this approach but it won't works in my case because OrderContextKey doesn't always follow same fields order and the combinations are more than 35. So I generate the loop on OrderContextKey.
I am looking for the solution other than joins because I had millions of data that cause memory issues while loading.
Hi,
at the end you need only OrderContextValue as key field. OrderContextKey has not more informations:
OrderContextKey:
LOAD Distinct
OrderContextKey
Resident Fact;
DROP Field OrderContextKey From Fact;
let subst=' & ' & chr(39) & '-' & chr(39) & ' & ';
for i=1 to FieldValueCount('OrderContextKey')
let vOCK=FieldValue('OrderContextKey',$(i));
let vFields=Replace('$(vOCK)','-','$(subst)');
Dims:
LOAD
$(vFields) as OrderContextValue,
*
Resident ContextDIM;
next
DROP Tables OrderContextKey,ContextDIM;
ContextDIM:
Left Keep(Fact)
LOAD * Resident Dims;
DROP Table Dims;
Regards
C.
"OrderContextKey had almost 35+ distinct combinations"
so your Dimension table has also 35+ columns, or all the 35+ distinct values are a combination of country,state and City?
You can also write an SQL statement, I created tables in DB as per your sample data, Below SQL works:
select
country,state,city,client,sum(amount) as amount
from(select country,state,city,client,amount
from ordw.DIMENSION a inner join ordw.FACT_TABLE b
on b.ORDERCONTEXTVALUE=a.COUNTRY and b.ORDERCONTEXTKEY='Country'
union all
select country,state,city,client,amount
from ordw.DIMENSION a inner join ordw.FACT_TABLE b
on b.ORDERCONTEXTVALUE=a.COUNTRY||'-'||a.STATE and b.ORDERCONTEXTKEY='Country-State'
union all
select country,state,city,client,amount
from ordw.DIMENSION a inner join ordw.FACT_TABLE b
on b.ORDERCONTEXTVALUE=a.COUNTRY||'-'||a.STATE||'-'||city
and b.ORDERCONTEXTKEY='Country-State-City'
union all
select country,state,city,client,amount
from ordw.DIMENSION a inner join ordw.FACT_TABLE b
on b.ORDERCONTEXTVALUE=a.STATE||'-'||city
and b.ORDERCONTEXTKEY='State-City'
)detailed
group by country,state,city,client
Result:
COUNTRY STATE CITY CLIENT AMOUNT
USA NY Buffalo A 280
USA CA Freemont B 186
USA NY Albany C 208
USA NJ Newark D 193
USA PA Allen Town E 194
USA NY New York F 272
USA CA Los Angels G 116
USA CA San Diego H 216
Hi,
maybe something like:
CountryDim:
LOAD RecNo() as LocationID, * FROM [https://community.qlik.com/servlet/JiveServlet/download/1150855-251565/ContextAssociation.xlsx] (ooxml, embedded labels, table is CountryDim);
Fact:
LOAD *, AutoNumber(OrderContextValue) as LocationKey FROM [https://community.qlik.com/servlet/JiveServlet/download/1150855-251565/ContextAssociation.xlsx] (ooxml, embedded labels, table is Fact);
CrossTable (TempField, LocationKey)
LOAD LocationID,
AutoNumber(Country),
AutoNumber(Country&'-'&State),
AutoNumber(Country&'-'&State&'-'&City),
AutoNumber(State&'-'&City)
Resident CountryDim;
DROP Field TempField;
hope this helps
regards
Marco