4 Replies Latest reply: Jun 15, 2015 2:45 PM by Hyrum Anderson RSS

    Merge data queries

    Hyrum Anderson

      I am loading monthly sales volume by product from an OLE DB connection and then I have my gross margin by product in a csv file. I want to be able to multiply the gross margin for a product by its respective volume to create graphs and KPI's. Is there a way I can merge the queries in the data load editor?

       

      I already tried just doing a calculated measure in the app overview and it isn't returning anything.

       

      Here is what my script looks like right now (minus proprietary details )

       

      //CSV Gross Margin Load

      LOAD

          Product as "Product Num",

          GM_Per_Lb

      FROM [lib://Gross Margin/Gross Margin.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

       

      //Sales History Load

      LIB CONNECT TO '****************************************';

      SQL SELECT Territory,

          "Bill To Name",

          "Ship To Name",

          "Ship Date",

          "Product Num",

          "Product Desc",

          "Actual Fill Weight",

       

      FROM "DC59_Live".dbo."ADGV_SO_Territory"

      LEFT OUTER JOIN "DC59_Live".dbo."en_prod_tbl"

      ON "Product Num" = "en_prod_key";

        • Re: Merge data queries
          Ganesh Reddy

          Hi Anderson,

           

          Use Applymap() for this scenario.(Use Product number as a key). Avoid joining , use appymap() it may increase the performance.

           

           

          Cheers,

          Ganesh.

          • Re: Merge data queries
            Xavier Retaillaud

            Hi,

             

            Your measure calculated in the application should return a value, we should look for what is wrong here.

             

            Anyway you can try this script :

             

            MAP1:
            MAPPING LOAD

                Product as "Product Num",

                GM_Per_Lb

            FROM [lib://Gross Margin/Gross Margin.csv]

            (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

             

            LIB CONNECT TO '****************************************';

            MyTable:
            LOAD
               *,
               ApplyMap ('MAP1', GM_Per_Lb) AS GM_Per_Lb;

            SQL SELECT Territory,

                "Bill To Name",

                "Ship To Name",

                "Ship Date",

                "Product Num",

                "Product Desc",

                "Actual Fill Weight",

             

            FROM "DC59_Live".dbo."ADGV_SO_Territory"

            LEFT OUTER JOIN "DC59_Live".dbo."en_prod_tbl"

            ON "Product Num" = "en_prod_key";

            • Re: Merge data queries
              Hyrum Anderson

              This is looking very promising. When I include the code you provided xavier_ch199 and run the debugger it gets to the "SQL SELECT Territory" and then throws the error "Field not found - <GM_Per_Lb>" do I need to do anything inside the SQL SELECT statement?

              • Re: Merge data queries
                Hyrum Anderson

                Okay I just got it, I had to put ApplyMap ('MAP1', "Product Num") AS GM_Per_Lb;  instead. Thanks Xavier and Ganesh for your help! I wouldn't have known to use ApplyMap without you.


                Thanks!