Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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
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
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.