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

How to Use Apply Map in QV Script with SQL Table

Tue, 14 Jan 2014 23:32:45 GMT

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?

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

jaimeaguilar — Tue, 14 Jan 2014 23:37:27 GMT

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

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

Wed, 15 Jan 2014 03:47:20 GMT

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

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

Sokkorn — Wed, 15 Jan 2014 06:43:08 GMT

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

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

Wed, 15 Jan 2014 07:02:03 GMT

Hi Shelley,

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

Santhosh G

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

Thu, 16 Jan 2014 21:21:27 GMT

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

Nicole-Smith — Thu, 16 Jan 2014 21:26:21 GMT

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)

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

Thu, 16 Jan 2014 21:32:44 GMT

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