topic Surpress unmatched records in QlikView

Surpress unmatched records

Wed, 21 Oct 2009 20:37:13 GMT

Hello

I'm loading several files into my QV application and letting QV join them together for me, which works fine. Is there a way to prevent QV showing unmatched records in a listbox?

So for example, I have a customer lookup file with Customer ID and Name, and a sales file with Customer ID, Product and Qty. QV automatically joins the two files together by Customer ID. There are some customers who have no matches on the sales file and I don't want those customers to appear in the Customer listbox. Can I get QV to automatically remove them?

Hope that makes sense, thanks for your help.

Surpress unmatched records

sathishkumar_go — Wed, 21 Oct 2009 21:05:42 GMT

Use alias for Sales file.

Example Customer ID AS Cid

and take Cid in list box

I hope it ll use for u

Regards,

Sathish

Surpress unmatched records

Wed, 21 Oct 2009 22:49:11 GMT

I usually use a left join in the script or if there is only one field I use the mapping function. If you use the Left Join, make sure the first field in your joined table is the CustomerID and you will add data only for the customers that exist in your other data. If you just want to add the Name, I would use the mapping function instead (2nd example).


Data:
LOAD
CustomerID,
Field1,
Field2
Resident DataSource;

Customer:
LEFT JOIN (Visit)
LOAD
CustomerID,
CustomerName,
OtherCustomerInfo
Resident CustomerData;


Customer:
MAPPING LOAD
CustomerID,
CustomerName
Resident CustomerData;
Data:
LOAD
CustomerID,
ApplyMap('Customer',CustomerID) as CustomerName,
Field1,
Field2
Resident DataSource;

Surpress unmatched records

Wed, 21 Oct 2009 22:51:50 GMT

Sorry the first one should read LEFT JOIN (Data) .