Concatenate or resident

Sun, 15 Dec 2013 01:18:36 GMT

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

Sun, 15 Dec 2013 02:56:45 GMT

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.

topic Concatenate or resident in QlikView

Concatenate or resident

Re: Concatenate or resident