7 Replies Latest reply: Jan 16, 2014 4:32 PM by Shelley Smith RSS

    How to Use Apply Map in QV Script with SQL Table

    Shelley Smith

      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
          Jaime Aguilar

          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
            Srikanth P

            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 Cheav

              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

                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
                  Shelley Smith

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