Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to Use Apply Map in QV Script with SQL Table

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? 

Tags (1)
1 Solution

Accepted Solutions

Re: How to Use Apply Map in QV Script with SQL Table

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)

7 Replies
jaimeaguilar
Valued Contributor II

Re: How to Use Apply Map in QV Script with SQL Table

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

Not applicable

Re: How to Use Apply Map in QV Script with SQL Table

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
Honored Contributor

Re: How to Use Apply Map in QV Script with SQL Table

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

Not applicable

Re: How to Use Apply Map in QV Script with SQL Table

Hi Shelley,

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

Santhosh G

Not applicable

Re: How to Use Apply Map in QV Script with SQL Table

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

Re: How to Use Apply Map in QV Script with SQL Table

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)

Not applicable

Re: How to Use Apply Map in QV Script with SQL Table

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

Community Browser