4 Replies Latest reply: Jul 21, 2016 11:10 AM by Damian Eralio RSS

    Comparing one SQL field to two Excel fields to find a match

    Alec Whitaker

      Hello,

       

      I am building a dashboard to report the available inventory quantity for parts. In the dashboard, I take a SQL file with the quantity of parts on hand (SQL Quantity) and divide it by an Excel spreadsheet with quantities (Excel quantities) established for the locations.

       

      Here is the problem I am having:

       

      When the Part Number in SQL does not match the Part Number in Excel, I need the Part Number in SQL to then compare itself to the Alternate Part Number in Excel. Essentially, the SQL Part Number must first compare itself to A, and if it does not find a match, I need it to compare itself to B to find a match.

       

      Can somebody please direct me on how to accomplish this? Currently, I have the Part Number in SQL and the Part Number in Excel named the same, so Qlikview links them together as a Key. How can I create this error validation, so if SQL <> Excel A, then SQL = Excel B?

       

      Thank you,

       

      Alec

        • Re: Comparing one SQL field to two Excel fields to find a match
          rodrigo silva

          I think you can implement something:

           

          excela:

          load a as keya,

               b

          from souce;

           

          sql:

          load a,

                    b

          from sql where exist(keya,a);

           

          excelb:

          load a as keyb,

               b as b1

          from souce;

           

          sql:

          load a,

                    b

          from sql where exist(keyb,a) and not exist (a);

            • Re: Comparing one SQL field to two Excel fields to find a match
              Alec Whitaker

              I'm sorry, I am confused by this. I have one column in SQL, (PartNumber) that I need to match to one column in Excel (PartNumber) and if it doesn't find a match I need it to look for a match in a separate column in Excel (AlternatePartNumber). These columns are both on the same Excel spreadsheet.

               

              I am getting confused on the 'keya', 'keyb', 'b1',etc keywords, as I think it should just be limited to the three fields above, PartNumber and AlternatePartNumber. Can you please elaborate on the proper fields? I would greatly appreciate it.

               

              Thank you,

               

              Alec

            • Re: Comparing one SQL field to two Excel fields to find a match
              Andrew Walker

              Hi Alec,

                             Say your excel file has fields, Product, CodeA and CodeB then

               

              XLProd:

              Load

              Product,

              CodeA as Code from ExcelFile;

              Load

              Product,

              CodeB as Code from ExcelFile;

               

              If you load from SQL with the key field Code it will associate with the field Prod in XLProd.

               

              Cheers

               

              Andrew

              • Re: Comparing one SQL field to two Excel fields to find a match
                Damian Eralio

                If you want to still be able to retain the relationship between your excel's Primary and Alternate part numbers, you might try something like this:

                 

                // First load the SQL table

                SQL:

                LOAD PartNumber,

                    Purchased

                FROM

                Parts.xlsx

                (ooxml, embedded labels, table is SQL);

                 

                // Next load only the excel records that match the the SQL in the Primary (Part)

                Excel:

                LOAD Part as PartNumber,

                  Part,

                  AltPart,

                    Color

                FROM

                Parts.xlsx

                (ooxml, embedded labels, table is Excel)

                WHERE exists(PartNumber,Part);

                 

                // Next load only the excel records that match the SQL in the Alternate (AltPart)

                // NOTE this table will auto-concatenate because all the fields match the previous table

                 

                Excel:

                LOAD AltPart as PartNumber,

                  Part,

                  AltPart,

                    Color

                FROM

                Parts.xlsx

                (ooxml, embedded labels, table is Excel)

                WHERE exists(PartNumber, AltPart);

                 

                In the test below I got the results I wanted and still was able to see the relationship between the original Primary and Alternate parts in the Excel.

                I would lose records in the excel where neither Primary or Alternate have a match. However, I can do a third load at this point with the concatenate command to bring those missing records in from the excel.