7 Replies Latest reply: Oct 30, 2016 8:14 PM by Marco Wedel RSS

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

    Srikanth P

      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  ?

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

          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.

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

            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.

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

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

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

                  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
                  Marco Wedel

                  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