How to Use Apply Map in QV Script with SQL Table

[#1]

Team,

Trying to use ApplyMap function.

I created a Mapping Table Like This:

[Primary Product Family Map]:

    SQL SELECT ID as DIM_SALES_PART_ID,

    LEFT("CATALOG_GROUP",1) as [PRIMARY_PRODUCT_FAMILY_CODE]

FROM "IFSDW_PROD".dbo."Dm_DIM_SALES_PART";

The above seems to work, but now how do I use the ApplyMap function with a table loaded with SQL?  I want to take DIM_SALES_PART_ID from the below and essentially use the ApplyMap function to load the PRIMARY_PRODUCT_FAMILY_CODE, but when I try to insert the ApplyMap command in the script below, I receive an error.

SQL SELECT "ORDER_NO",

    QTY as Qty,

    "NET_AMOUNT_IN_BASE" as [Net Revenue],

    "COST_PER_UNIT" as [Std Cost],

     "INVOICE_DATE",

    YEAR(INVOICE_DATE) as [Ship Yr],

    MONTH(INVOICE_DATE) as [Ship Mo],

    DAY(INVOICE_DATE) as [Ship Dy],

    "DIM_CUSTOMER_ID" as [Customer #],

    "DIM_SALES_PART_ID",

    ApplyMap('[Primary Product Family Map]', DIM_SALES_PART_ID) as [Primary Product Family],

    SOURCE

FROM "IFSDW_PROD".dbo."Dm_FACT_DISTRIBUTOR_SALES";

ErrorMsg: 'ApplyMap' is not a recognized built-in function name.

Is there a way to load table with SQL, but then use QV ApplyMapy function? 

[jaimeaguilar]

Hi,

you have to use the applymap function in the Load section (this is where you can use QlikView functions), something like this:

TableName:

Load *,

        ApplyMap('[Primary Product Family Map]', DIM_SALES_PART_ID) as [Primary Product Family];

SQL SELECT "ORDER_NO",

    QTY as Qty,

    "NET_AMOUNT_IN_BASE" as [Net Revenue],

    "COST_PER_UNIT" as [Std Cost],

     "INVOICE_DATE",

    YEAR(INVOICE_DATE) as [Ship Yr],

    MONTH(INVOICE_DATE) as [Ship Mo],

    DAY(INVOICE_DATE) as [Ship Dy],

    "DIM_CUSTOMER_ID" as [Customer #],

    "DIM_SALES_PART_ID",

    SOURCE

FROM "IFSDW_PROD".dbo."Dm_FACT_DISTRIBUTOR_SALES";

otherwise, you'll get an error because what you type after SQL keyword will be interpreted as SQL script,

regards

[#3]

First you need to create the Mapping table with Mapping key word  and then use ApplyMap function in the precedent Load like below:

[Primary Product Family Map]:

    Mapping

    SQL SELECT ID as DIM_SALES_PART_ID,

    LEFT("CATALOG_GROUP",1) as [PRIMARY_PRODUCT_FAMILY_CODE]

FROM "IFSDW_PROD".dbo."Dm_DIM_SALES_PART";

Table:

LOAD * , ApplyMap('[Primary Product Family Map]', DIM_SALES_PART_ID) as [Primary Product Family] ;

SQL SELECT "ORDER_NO",

    QTY as Qty,

    "NET_AMOUNT_IN_BASE" as [Net Revenue],

    "COST_PER_UNIT" as [Std Cost],

     "INVOICE_DATE",

    YEAR(INVOICE_DATE) as [Ship Yr],

    MONTH(INVOICE_DATE) as [Ship Mo],

    DAY(INVOICE_DATE) as [Ship Dy],

    "DIM_CUSTOMER_ID" as [Customer #],

    "DIM_SALES_PART_ID",

     SOURCE

FROM "IFSDW_PROD".dbo."Dm_FACT_DISTRIBUTOR_SALES";

[Sokkorn]

Hi Shelley,

You may consider script below

[PrimaryProductFamilyMap]:
Mapping
Load
  ID AS [DIM_SALES_PART_ID],
  Left(CATALOG_GROUP,1) AS [PRIMARY_PRODUCT_FAMILY_CODE];
SQL SELECT ID, CATALOG_GROUP
FROM "IFSDW_PROD".dbo."Dm_DIM_SALES_PART";


[TableName]:
Load
  *,
  ApplyMap('PrimaryProductFamilyMap',DIM_SALES_PART_ID) AS [Primary Product Family];
SQL SELECT "ORDER_NO",
    QTY as Qty,
    "NET_AMOUNT_IN_BASE" as [Net Revenue],
    "COST_PER_UNIT" as [Std Cost],
     "INVOICE_DATE",
    YEAR(INVOICE_DATE) as [Ship Yr],
    MONTH(INVOICE_DATE) as [Ship Mo],
    DAY(INVOICE_DATE) as [Ship Dy],
    "DIM_CUSTOMER_ID" as [Customer #],
    "DIM_SALES_PART_ID",
    SOURCE
FROM "IFSDW_PROD".dbo."Dm_FACT_DISTRIBUTOR_SALES";

Regards,

Sokkorn

[#5]

Hi Shelley,

This is pur Qlikview function you can use this on QVD's Data.

Santhosh G

[#6]

Team,

Thanks for the help as I did get rid of the error, but it appears the mapping is still not populating in my table. I must still be missing something.  Here's what I have at this point.  Thanks for any help or insight you can provide!

[Primary Product Family Map]:

MAPPING

SQL SELECT ID as DIM_SALES_PART_ID,

    LEFT("CATALOG_GROUP",1) as [PRIMARY_PRODUCT_FAMILY_CODE]

FROM "IFSDW_PROD".dbo."Dm_DIM_SALES_PART";

//****Load Sales Plan with Sales Data****

[Sales Data]:

Load *, ApplyMap('[Primary Product Family Map]', DIM_SALES_PART_ID) as [PRIMARY_PRODUCT_FAMILY_CODE];

SQL SELECT "ORDER_NO",

    QTY as Qty,

    "NET_AMOUNT_IN_BASE" as [Net Revenue],

    "COST_PER_UNIT" as [Std Cost],

    "INVOICE_DATE",

    YEAR(INVOICE_DATE) as [Ship Yr],

    MONTH(INVOICE_DATE) as [Ship Mo],

    DAY(INVOICE_DATE) as [Ship Dy],

    "CREATED_DATE",

    YEAR(CREATED_DATE) as [Incoming Yr],

    MONTH(CREATED_DATE) as [Incoming Mo],

    DAY(CREATED_DATE) as [Incoming Dy],

    "DIM_SITE_ID" as [Site],

    "DIM_CUSTOMER_ID" as [Customer #],

   "DIM_SALES_PART_ID",

    SOURCE

FROM "IFSDW_PROD".dbo."Dm_FACT_DISTRIBUTOR_SALES";

[Sales Plan]:

CONCATENATE ([Sales Data])

SQL SELECT "DIM_SITE_ID" as [Site],

    "CUSTOMER_ID" as [Customer #],

    "ST_COUNTY_FIPS" as FIPS,

    YEAR as [Ship Yr],

    "MONTH_NUMBER" as [Ship Mo],

    "MARKET_CODE" as Market,

    "MARKET_QUOTA" as Quota,

    "MARKET_POTENTIAL" as [Potential],

    "WHITE_SPACE" as [White Space Goal],

    "PRIMARY_PRODUCT_FAMILY_CODE"

FROM "IFSDW_DEV".dbo."Dm_FACT_SALES_PLAN";

[Nicole-Smith · Accepted solution]

You need to take the brackets off of the map name:

ApplyMap('[Primary Product Family Map]', DIM_SALES_PART_ID)

should be

ApplyMap('Primary Product Family Map', DIM_SALES_PART_ID)

[#8]

Nicole Smith,

You are awesome!  It works now!  I was hoping it was something small, but was pulling my hair out trying things and reloading.

Thanks so much for your help,

Shelley