Where Clause

[#1]

Hey guys.

I got a minor ssue. I got a bunch of vendors in a list but only a few of them have sales. Now they have 2 fields namely Preffered vendor status and active vendor. How do i do a calculation in the script using the where clause that displays the vendors that made sales according to active vendor and preffered vendor status? my script is as follows:

Vendor:

LOAD ProductID,

BusinessEntityID as %_BusinessEntityIDKey,

AverageLeadTime,

StandardPrice,

LastReceiptCost,

LastReceiptDate,

MinOrderQty,

MaxOrderQty,

OnOrderQty,

UnitMeasureCode;

SQL SELECT *

FROM ProductVendor;

LEFT JOIN('Vendor')

LOAD BusinessEntityID as %BusinessEntityIDKey,

AccountNumber as %_AccountNumberKey,

Name as VendorName,

CreditRating,

PreferredVendorStatus,

ActiveFlag;

SQL SELECT *

FROM Vendor;

Any help is much appreciated.

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

If I understood your question right, you may use

LEFT JOIN('Vendor')LOAD BusinessEntityID as %BusinessEntityIDKey, AccountNumber as %_AccountNumberKey, Name as VendorName, CreditRating, PreferredVendorStatus, ActiveFlag;SQL SELECT *FROM Vendor WHERE PreferredVendorStatus = 1 AND ActiveFlag = 2;

or

LEFT JOIN('Vendor') LOAD BusinessEntityID as %BusinessEntityIDKey, AccountNumber as %_AccountNumberKey, Name as VendorName, CreditRating, PreferredVendorStatus, ActiveFlagWHERE PreferredVendorStatus = 'Something' AND ActiveFlag = 'Something Else'; // You can use here QlikView functionsSQL SELECT * FROM Vendor;

Hope that helps

[#3]

Hi Miguel

Thanks for the reply. Yes that is what i want to achieve, however, when i create a list box, instead of only the active vendors and preferred vendors dsplaying, they all do. Is there a way to make them load only the vendors who are active and preferred?

Thanks so much for your help much appreciated.

[Miguel_Angel_Baeyens]

Hello,

Yes, it should return only the values matching the WHERE clause. I suggest you try first the raw sql statement in your query analyzer, just to check that the query is returning the correct records and that the WHERE statement is written properly.

Take into account some difficult values to deal with as nulls, empty, or something.

Regards