In SQL statements, it is very useful to use IN clause in the query to compare your value against the provided list of values .
In SQL you can write a query such as
Select ProductName,Revenue from Products
where ProductName IN (‘Chairs’,’Tables’,’CrossArmChairs’);
QlikView does not have IN operator instead QlikView provide match() function to achieve this functionality
I will load an Inline table
Now to load a table with ProductName IN (‘Chairs’,’Tables’,’ArmChairs’) you will use match()
match() does case sensitive comparision. It will return 1 if ProductName = Chairs but will return 0 if ProductName = chairs
For case insenstive comparison, you can use mixmatch()
wildmatch() helps in case insensitive comparison and also permits the use of wild characters
You can use Not preceding Match() to get the ProductNames that are NOT IN the list of values
To load ProductNames which are not ‘Boats’ , you can write
Match() can be used in an expression while creating a chart.
To learn about using Match function to group dimensions in Qlik Sense, visit https://www.learnallbi.com/how-to-create-master-items-in-qlik-sense/
3 thoughts on “Using IN clause in QlikView”
good it is helpful
ihave one doubt
in my qvd have 100000 records but i want load only 100 records how can restrict u? pls give me explanation
You can do limited load to load only 100 records.
In the script editor, click on debug and put your desired number of records under Limited Load.
How to merge information from different sources.
After loading a CSV data, enrich it with specific data from an SQL datasource.
[cpf] as uniqueID,
(txt, utf8, embedded labels, delimiter is ‘,’, msq);
LIB CONNECT TO [Microsoft_SQL_Server_PDW];