5 Replies Latest reply: Sep 6, 2016 10:21 AM by patrico mesri RSS

    If between excels in load.

    patrico mesri

      Hi Community. Hope you guys can help me out with this problem.

      I'm loading 2 excels into my .qvf. The second Load is using an IF expression using a field from the first excel.

      When trying to load it, an error pops out saying that Field "CMv" was not found.

       

      LOAD
          Soc.,

          Material,
          CMv,
          "Valor de venta",
          "Valor PV con IVA"
      FROM [lib://Agro/MB51 - Movimientos stock  del 01.07.15 al 30.06.16.xlsx]
      (ooxml, embedded labels, table is Hoja1)
      Where Material >= 200000 and Material <= 299999;

       

       

      LOAD
          WERKS,
          LGORT as "Cl.valor.",
          LGOBE,
          if(CMv='Z15' or CMv='Z16' or CMv='Z17' or CMv='Z18' or CMv='Z39' or CMv='Z40' ,LGOBE) as LGOBEGRANOS,
          if(CMv='Z05' or CMv='Z06' or CMv='Z43' or CMv='Z44' ,LGOBE) as LGOBECAMPOS,
          OIG_ITRFL,
          OIB_TNKASSIGN
      FROM [lib://Agro/T001L.XLS]
      (ooxml, embedded labels, table is Sheet1);

       

       

      Thank you in advance.

      Regards.

        • Re: If between excels in load.
          Sunny Talwar

          You will need to bring CMv into your second Excel file. To do that, you have few options

           

          1) Use Lookup function

          2) Use ApplyMap function

          3) Join the tables together

          • Re: If between excels in load.
            balraj ahlawat

            Does this CMv field exist in your excel sheet?

            • Re: If between excels in load.
              Sinan Ozdemir

              Hi,

               

              I don't think this will work because it is pulling the data from the spreadsheet and CMv doesn't exist in lib://Agro/T001L.XLS. What I would suggest is to use Apply Map function.

               

              Hope this helps

              • Re: If between excels in load.
                Miguel Braga

                The answer is simple, mate

                 

                You need to concatenate and/or left join the fields from the first sheet to the table of the second sheet. To do that you must invoke a Field Key to link the two tables. Only then you can do something like this:

                 

                Table1:

                LOAD
                    Soc.,

                    Material,
                    CMv,
                    "Valor de venta",
                    "Valor PV con IVA",

                     [%Key Field]
                FROM [lib://Agro/MB51 - Movimientos stock  del 01.07.15 al 30.06.16.xlsx]
                (ooxml, embedded labels, table is Hoja1)
                Where Material >= 200000 and Material <= 299999;

                 

                left join (Table1)

                LOAD

                    WERKS,

                    LGORT as "Cl.valor.",

                    LGOBE,

                    OIG_ITRFL,

                    OIB_TNKASSIGN,

                    [%Key Field]

                FROM [lib://Agro/T001L.XLS]

                (ooxml, embedded labels, table is Sheet1);

                 

                Table:

                LOAD *,

                          if(CMv='Z15' or CMv='Z16' or CMv='Z17' or CMv='Z18' or CMv='Z39' or CMv='Z40' ,LGOBE) as LGOBEGRANOS,

                          if(CMv='Z05' or CMv='Z06' or CMv='Z43' or CMv='Z44' ,LGOBE) as LGOBECAMPOS;

                LOAD *

                Resident Table1;

                 

                Drop Table Table1;

                 

                Hope this helps you

                 

                Regards,

                MB