Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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;

Tags (2)
1 Reply
Not applicable

Re: Concatenate or resident

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.