1 Reply Latest reply: Dec 14, 2013 9:56 PM by Srikanth P RSS

    Concatenate or resident

      How do i get this model most perfect? I want Lng and Lat down into my FACT.  (they should be linked together by CustomerID)

       

      I have this Dimension

      QUALIFY*;

      UNQUALIFY SessionCustomerKey;

      DimCustomer:

      LOAD CustomerKey as SessionCustomerKey,

          PartnerID,

          CustomerID,

          PartnerKey,

          CustomerKey,

          CustomerGuid,

          Lng,

          Lat;

      SQL SELECT *

      FROM dbo.DimCustomer;

      UNQUALIFY*;

       

      And this Fact:

      FactRows:

      LOAD TotalRowsKey,

          CustomerKey,

          CustomerID,

          AntalRowsCleansed,

          AntalRowsStaCloud,

          CreateDate;

      SQL SELECT *

      FROM RaptorMetaCustomer.dbo.TotalRows

      Order by CreateDate Desc;

        • Re: Concatenate or resident
          Srikanth P

          You can get by Left join or ApplyMap. I always prefer ApplyMap if you have unique combination. Please try either of any one.

           

          ApplyMap Method:

           

          QUALIFY*;

          UNQUALIFY SessionCustomerKey;

          DimCustomer:

          LOAD CustomerKey as SessionCustomerKey,

              PartnerID,

              CustomerID,

              PartnerKey,

              CustomerKey,

              CustomerGuid,

              Lng,

              Lat;

          SQL SELECT *

          FROM dbo.DimCustomer;

          UNQUALIFY*;

           

          MAP_LAN:

          MAPPING LOAD DISTINCT DimCustomer.CustomerID AS A , DimCustomer.Lng AS B Resident DimCustomer ;

           

          MAP_LAT:

          MAPPING LOAD DISTINCT DimCustomer.CustomerID AS A , DimCustomer.Lat AS B Resident DimCustomer ;

           

          DROP Fields DimCustomer.Lng , DimCustomer.Lat; //Drop these fields if not needed in DimCustomer table.

           

          FactRows:

          LOAD TotalRowsKey,

              CustomerKey,

              CustomerID,

              ApplyMap('MAP_LAN', CustomerID, 'NA') AS Lan,

              ApplyMap('MAP_LAT', CustomerID, 'NA') AS Lat,

              AntalRowsCleansed,

              AntalRowsStaCloud,

              CreateDate;

          SQL SELECT *

          FROM RaptorMetaCustomer.dbo.TotalRows

          Order by CreateDate Desc;

           

          Left Join Method:

           

          QUALIFY*;

          UNQUALIFY SessionCustomerKey;

          DimCustomer:

          LOAD CustomerKey as SessionCustomerKey,

              PartnerID,

              CustomerID,

              PartnerKey,

              CustomerKey,

              CustomerGuid,

              Lng,

              Lat;

          SQL SELECT *

          FROM dbo.DimCustomer;

          UNQUALIFY*;

           

          FactRows:

          LOAD TotalRowsKey,

              CustomerKey,

              CustomerID,

            ApplyMap('MAP_LAN', CustomerID, 'NA') AS Lan,

            ApplyMap('MAP_LAT', CustomerID, 'NA') AS Lat,

              AntalRowsCleansed,

              AntalRowsStaCloud,

              CreateDate;

          SQL SELECT *

          FROM RaptorMetaCustomer.dbo.TotalRows

          Order by CreateDate Desc;

          Left Join (FactRows)

          LOAD DimCustomer.CustomerID AS CustomerID ,

            DimCustomer.Lng AS Lng,

            DimCustomer.Lat AS Lat

          Resident DimCustomer ;

           

          DROP Fields DimCustomer.Lng , DimCustomer.Lat; //Drop these fields if not needed in DimCustomer table.