12 Replies Latest reply: Dec 3, 2013 5:32 PM by Reddy Jithender RSS

    Not able to  understand apply map :new to qlikview

      Hi i have 6 tables need to use applu map and join these into one table

       

      Please find the table script structure :

      LOAD SUB_COMMODITY_CODE,
           SUB_COMMODITY_CODE_DESC
      FROM
      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\SUB_COMMODITY .qvd]
      (qvd);

      LOAD ID,
           GLOBAL_SUPPLIER_ID,
           CAPABILITY_ID
      FROM
      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\SUPPLIER_CAPABILITIES .qvd]
      (qvd);

      LOAD COMMODITY_CODE,
           CAPABILITY_ID,
           CAPABILITY
      FROM
      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\CAPABILITIES .qvd]
      (qvd);

      LOAD COMMODITY_CODE,
           COMMODITY_CODE_DESC
      FROM
      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\COMMODITY .qvd]
      (qvd);

      LOAD SUB_COMMODITY_CODE,
           COMMODITY_MANAGER
      FROM
      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\COMMODITY_MANAGER .qvd]
      (qvd);

      LOAD ID,
           GLOBAL_SUPPLIER_ID,
           SUB_COMMODITY_CODE
      FROM
      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\PREFRD_SUPPLIER_COMMODITY .qvd]
      (qvd);

       

       

      Please gudie me on this

       

        • Re: Not able to  understand apply map :new to qlikview
          Bill Markham

          Reddy

           

          This Blog Post Don't join - use Applymap instead by Henric Cronström is good.

           

           

          Best Regards,     Bill

          • Re: Not able to  understand apply map :new to qlikview

             

            COMMODITY:

            Mapping LOAD
            COMMODITY_CODE,
            COMMODITY_CODE_DESC

            FROM
            [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\COMMODITY .qvd]
            (
            qvd);

            COMMODITY_MANAGER:
            Mapping LOAD SUB_COMMODITY_CODE,
            COMMODITY_MANAGER
            FROM
            [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\COMMODITY_MANAGER .qvd]
            (
            qvd);

            //LOAD ID,
            // GLOBAL_SUPPLIER_ID,
            // SUB_COMMODITY_CODE
            //FROM
            //[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\PREFRD_SUPPLIER_COMMODITY .qvd]
            //(qvd);
            SUPPLIER_CAPABILITIES:
            LOAD ID,
            GLOBAL_SUPPLIER_ID as [GLOBAL_SUPPLIER_CODE],
            CAPABILITY_ID,
            ApplyMap('COMMODITY_MANAGER',CAPABILITY_ID,Null()) As COMMODITY_MANAGER

            FROM
            [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\SUPPLIER_CAPABILITIES .qvd]
            (
            qvd);
            Join
            LOAD COMMODITY_CODE,
            CAPABILITY_ID,
            CAPABILITY
            FROM
            [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\CAPABILITIES .qvd]
            (
            qvd);

            LOAD * Resident SUPPLIER_CAPABILITIES;
            Join
            LOAD SUB_COMMODITY_CODE,
            SUB_COMMODITY_CODE_DESC,
            ApplyMap('COMMODITY', COMMODITY_CODE,Null()) As COMMODITY_CODE_DESC

            FROM
            [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\SUB_COMMODITY .qvd]
            (
            qvd);

            where i am going wrong ..??

              • Re: Re: Not able to  understand apply map :new to qlikview
                Bill Markham

                Reddy

                 

                I have just taken a couple of bits

                 

                COMMODITY:

                Mapping LOAD

                COMMODITY_CODE,

                COMMODITY_CODE_DESC

                FROM

                [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\COMMODITY .qvd]

                (qvd);

                 

                ResultTable:

                LOAD

                COMMODITY_CODE,

                applymap ( 'COMMODITY' , COMMODITY_CODE ) as COMMODITY_CODE_DESC ,

                CAPABILITY_ID,

                CAPABILITY

                FROM

                [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\CAPABILITIES .qvd]

                (qvd);

                 

                And added an Applymap into it.

                 

                Assuming it works (I cannot test without your qvd's) does this example help you ?

                 

                 

                Best Regards,     Bill

                • Re: Not able to  understand apply map :new to qlikview
                  Alexis Hadjisoteriou

                  Hi Reddy,

                   

                  There are a few comments that I can make looking at your code. Some are just good coding practises and some are perhaps reasons why your code is not working.

                   

                  Starting from the top, let's take each table in turn:

                  a) COMMODITY table - no problems

                   

                  b) COMMODITY_MANAGER: Here you have named one of the fields (COMMODITY_MANAGER) the same as the table - this is not a good programming practice and best not used to avoid confusion. One way that I usually get around naming my tables is to use the "plural" for the table name so in this case "COMMODITY_MANAGERS"

                   

                  c) SUPPLIER_CAPABILITIES:
                  In this instance your ApplyMap statement is calling "COMMODITY_MANAGER" (the table) passing the CAPABILITY_ID and getting back the "COMMODITY_MANAGER" from the table "COMMODITY_MANAGER" and you decide to name the result .... COMMODITY_MANAGER!!!  Apart from being totally confusing (because you have the same name for the field and table you are calling) but naming the result COMMODITY_MANAGER again, does add to the confusion but more importantly, establishes a link between the field "COMMODITY_MANAGER" in SUPPLIER_CAPABILITIES and "COMMODITY_MANAGER" in COMMODITY_MANAGER" - was that what you intended to do?

                   

                  Further down you JOIN with the set of data that is coming from CAPABILITIES.QVD - this seems fine as you are joining on one common field, the CAPABILITY_ID

                   

                  Beyond this point I lose you as you appear to be loading the very table that you are constructing (SUPPLIER_CAPABILITIES)  without creating another table just before the LOAD statement below, which is what I presume you wanted to do. In other words, whilst you are still in the scope of SUPPLIER_CAPABILITIES.

                   

                  Even if that worked you are then attempting to JOIN with the contents of the SUB_COMMODITY QVD file where you do not have ANY fields in common. None of SUB_COMMODITY_CODE, SUB_COMMODITY_CODE or COMMODITY_CODE DESC are present in the SUPPLIER_CAPABILITIES so there would be no join happening there.


                  LOAD * Resident SUPPLIER_CAPABILITIES;
                  Join
                  LOAD SUB_COMMODITY_CODE,
                  SUB_COMMODITY_CODE_DESC,
                  ApplyMap('COMMODITY', COMMODITY_CODE,Null()) As COMMODITY_CODE_DESC

                  FROM ......

                   

                  Hope this helps in getting your code to work.

                   

                  Alexis

                    • Re: Not able to  understand apply map :new to qlikview

                      That was helpful ..stil couldnt figure it out

                      please find my requirment:


                      These are the 6 tables i have and thier structure  i need to join to fact table with Global_supplier_id

                       

                      I need these fields

                      SUB_COMMODITY_CODE,

                           SUB_COMMODITY_CODE_DESC

                      CAPABILITY_ID

                      COMMODITY_CODE

                      CAPABILITY

                      COMMODITY_CODE_DESC

                      COMMODITY_MANAGER

                      GLOBAL_SUPPLIER_ID TO JOIN WITH MY FACT TABLE

                       

                      Please find the each table structure.

                       

                       

                      LOAD SUB_COMMODITY_CODE,
                           SUB_COMMODITY_CODE_DESC
                      FROM
                      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\SUB_COMMODITY .qvd]
                      (qvd);

                      LOAD ID,
                           GLOBAL_SUPPLIER_ID,
                           CAPABILITY_ID
                      FROM
                      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\SUPPLIER_CAPABILITIES .qvd]
                      (qvd);

                      LOAD COMMODITY_CODE,
                           CAPABILITY_ID,
                           CAPABILITY
                      FROM
                      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\CAPABILITIES .qvd]
                      (qvd);

                      LOAD COMMODITY_CODE,
                           COMMODITY_CODE_DESC
                      FROM
                      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\COMMODITY .qvd]
                      (qvd);

                      LOAD SUB_COMMODITY_CODE,
                           COMMODITY_MANAGER
                      FROM
                      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\COMMODITY_MANAGER .qvd]
                      (qvd);

                      LOAD ID,
                           GLOBAL_SUPPLIER_ID,
                           SUB_COMMODITY_CODE
                      FROM
                      [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\PREFRD_SUPPLIER_COMMODITY .qvd]
                      (qvd);

                  • Re: Not able to  understand apply map :new to qlikview
                    Alexis Hadjisoteriou

                    Taking the QVDs that you are using and a lot of guess-work I have nearly solved your issues/requirements but certain questions still remain.

                     

                    SUB_COMMODITY_MAP:

                    MAPPING LOAD SUB_COMMODITY_CODE,

                      SUB_COMMODITY_CODE_DESC

                    FROM

                    [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\SUB_COMMODITY .qvd]

                    (qvd);

                     

                    COMMODITY_MANAGER_MAP:

                    Mapping LOAD SUB_COMMODITY_CODE,

                      COMMODITY_MANAGER

                    FROM

                    [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\COMMODITY_MANAGER .qvd]

                    (qvd);

                     

                    COMMODITY:

                    Mapping LOAD COMMODITY_CODE,

                      COMMODITY_CODE_DESC

                    FROM

                    [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\COMMODITY .qvd]

                    (qvd);

                     

                     

                    CAPABILITIES:

                    LOAD COMMODITY_CODE,

                      CAPABILITY_ID,

                      CAPABILITY

                    FROM

                    [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\CAPABILITIES .qvd]

                    (qvd);

                     

                    SUPPLIER_CAPABILITIES_MAP:

                    MAPPING LOAD

                      //ID,

                      //GLOBAL_SUPPLIER_ID as [GLOBAL_SUPPLIER_CODE],

                      ID & '/' & GLOBAL_SUPPLIER_ID As SupplierCap_To_PrefSupplier_KEY

                      CAPABILITY_ID,

                    FROM

                    [\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\SUPPLIER_CAPABILITIES .qvd]

                    (qvd);

                     

                    PREF_SUPPLIER:

                    LOAD *,

                      // Here I would use the CapabilityID (see below) to connect with CAPABILITIES and COMMODITY above

                      // but cannot see how I can "connect" as we don't appear to have uniqueness to extract either the

                      // capability or commodity.. so that is the answer that you need to provide - what is the relationship

                      // between suppliers, commodities and capabilities

                    ;

                    LOAD ID,

                          GLOBAL_SUPPLIER_ID,

                      // I am assuming here that the combination of ID and GLOBAL_SUPPLIER_ID will give us a unique CAPABILITY_ID

                      ApplyMap('SUPPLIER_CAPABILITIES_MAP', ID & '/' & GLOBAL_SUPPLIER_ID, Null()) As CapabilityID,

                          SUB_COMMODITY_CODE,

                      ApplyMap('SUB_COMMODITY_MAP',SUB_COMMODITY_CODE, Null()) As SubCommodityDescription,

                      ApplyMap('COMMODITY_MANAGER_MAP',SUB_COMMODITY_CODE, Null()) As CommodityManager

                    FROM

                    [\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\PREFRD_SUPPLIER_COMMODITY .qvd]

                    (qvd);

                    • Re: Not able to  understand apply map :new to qlikview
                      Steve Dark

                      Hi Reddy,

                       

                      My blog post on ApplyMap breaks gives examples of the simple use of the statement, as well as some more advanced techniques you can use with it:

                       

                      http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

                       

                      Hope that helps,

                      Steve

                      • Re: Not able to  understand apply map :new to qlikview
                        Alexis Hadjisoteriou

                        Hi Reddy

                         

                        I see that, just like myself (especially when I first started) you are relying on the forums for many of your QlikView programming questions. This community has been a Godsend to me and I am sure many 1000s of others.

                         

                        People here are amazingly helpful and generous with their willingness to help their fellow Qlikviewers so it is good if we acknowledge this help by marking a reply as "helpful" or "correct" not only as a means for others to use your question and "correct answer" or "helpful answer" as a fast-track to getting answers to similar questions, but more importantly as a show of appreciation for the time that people spend, often with complete working applications, helping us. That way,it keeps the community active and friendly

                        best regards

                        Alexis