topic Re: Comparing one SQL field to two Excel fields to find a match in QlikView

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

Thu, 21 Jul 2016 12:52:46 GMT

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

Thu, 21 Jul 2016 13:29:10 GMT

I think you can implement something:

excela:

load a as keya,

from souce;

sql:

load a,

from sql where exist(keya,a);

excelb:

load a as keyb,

b as b1

from souce;

sql:

load a,

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

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

Thu, 21 Jul 2016 13:47:51 GMT

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

effinty2112 — Thu, 21 Jul 2016 15:07:46 GMT

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

deec — Thu, 21 Jul 2016 15:10:26 GMT

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.