Re: Context Based (either one field or more than f... - Qlik Community

Report Inappropriate Content · ‎2016-10-27

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‌ ?

Nicole-Smith · ‎2016-10-27

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.

Report Inappropriate Content · ‎2016-10-28

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.

cwolf · ‎2016-10-28

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.

Anonymous · ‎2016-10-28

"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?

Anonymous · ‎2016-10-28

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

MarcoWedel · ‎2016-10-30

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

Context Based (either one field or more than field) Association