Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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:     

OrderIDOrderContextKeyOrderContextValueAmount
1CountryUSA116
2Country-StateUSA-NY56
3State-CityNY-Albany36
4State-CityNY-New York100
5State-CityCA-Freemont70
6State-CityNJ-Newark77
7State-CityNY-Buffalo108
8Country-StateUSA-PA78
9Country-State-CityUSA-CA-San Diego100


Country Dimension Table:

   

CountryStateCityClient
USANYBuffaloA
USACAFreemontB
USANYAlbanyC
USANJNewarkD
USAPAAllen TownE
USANYNew YorkF
USACALos AngelsG
USACASan DiegoH

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

6 Replies
Highlighted

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

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.

Not applicable

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

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.

chrwolf64
Contributor III

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

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.

viveknair1234
New Contributor III

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

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

viveknair1234
New Contributor III

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

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

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

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