QlikView Where Exists

Overviews QlikView Where Exists

• QlikView where exists is the function which defines in the load script whether the value of any specific field has been loaded previously into the field.
• The Exists function outputs TRUE or FALSE, therefore it can be implemented in the WHERE clause of an IF function or a LOAD statement. Whereas the function Not Exists() can be applied to conclude if a field value is not loaded.
• This function in QlikView, helps to check both the formerly loaded values as well as formerly loaded tables in the present table. Thus, the primary occurrence only will be loaded but if the next occurrence is run into, then the value will be loaded formerly.

Key Takeaways

1. The where exists function filters an ongoing load in the recent row by analysing a particular column and testing if the value is present in a named field in that column.

2. If the version of Exists function has single parameter then it will ensure if the value in the table column fieldname prevails in the field having similar name. But previously beginning the recent load, that field may not be present, suppose like if the user need to escape loading the replicas.

3. Also, when the version of Exists function contains two parameters then, it checks the particular field provided in the first parameter to test the value indicated in the next parameter. In the current table row, normally this next parameter includes of an inversely termed cell or can be a concatenation of several cells in the same row.

QlikView Where Exists Field

The syntax of QlikView where exists function is written as follows:

Exists(field_name[, expr])

This function QlikView where exists field gives Boolean type data as result in return when executed. Let us discuss in brief about the arguments used in the syntax of the function above as follows:

• Field_name: It denotes the name of the field that is used to search any value. Even the user can implement an explicit field name but in absence of quotes. Previously, the field should be loaded using the script. Hence, it means that a field cannot be referred additionally which is loaded in a clause down in the script.
• Expr: This is the value which one need to test whether it is existing. Here, any expression or any explicit value can be used which denotes to a single or many fields in the existing load statement. It should be noted that fields cannot be referred which are not contained in the existing load statement. Expr argument is not required type, it’s optional. If we do not use it, then the exists function will check if the field_name value already exists in the recent record or not.

For instance,

1. Exists(Product)- Returns 1 (i.e. TRUE), when the value of the Product field in the recent record previously exists in any before read record comprising that field.

2. Exists(Product, ‘Rate’)- Returns 1 (i.e. TRUE), when the value of field named Rate is present or, available in the recent content of the Product field.

3. WHERE EXISTS- Suppose we have code as:

Staffs:

LOAD*Inline [
Staff|S_ID|Salary
Ankit|011|10000
Muskan|012|15000
Ritik|013|20000
] (delimiter is ‘|’);

Persons:

LOAD*Inline [
Staff|Address
Ankit|Delhi
Mary|Lucknow
Muskan|Indore
Nancy|Chennai
Ritik|Banglore
] (delimiter is ‘|’) WHERE Exists (Staff);

Drop Table Staffs;

It results are loaded in a new StaffAddress table which contains the names form the Persons table that are also present in the Staffs table. Also, to remove confusion the code deletes the both former tables.

Staff	Address
Ankit	Delhi
Muskan	Indore
Ritik	Banglore

QlikView where exists problem

• For working of the Exists function properly as expected then, it can be possible only if the field has a unique key where it is implemented. But in case if it is not so then, the initial occurrence of the of the key is only taken into consideration by this function.
• The QlikView where exits function does not execute properly in script.
• If the where exists function is used in an SQL SELECT statement, then it does not work since the exists function belongs to the QlikView function so it only can be used in a LOAD statement.
• With the release Qlik Sense Nov 18, the exists function encounters some errors in loading scripts.
• On running the where exists function, it gives field not found error if field names are not similar or parameter not set properly.

QlikView where exists tables

Using the QlikView where exists, we can check whether the table in the database previously exists or not. Here is provided a simple, firm and one-line code for testing in QlikView load script whether a specific table is present already and then delete it using the code below:

If(len(TableNumber(‘Name of the table to check ’)) > 0) THEN;
DROP Table [Name of the table to check];
End IF

It should be noted that ; is present after THEN. Here, the function TableNumber() is used to return the number of the table in the QlikView data model using its name.

Frequently Asked Questions (FAQs)

1. Using Where exists() function to load records from a dimension table but on execution it gives field not found issue?

Answer: The Exists function may take a couple of parameters if the table column names are not identical or if the user need to implement an expression. You should try to keep the load optimized for the qvd load and use underscore for variable names as: Load ‘Product code’ as ‘Product_cd’.

2. How to check if a field exists in a QVD contained with constant values?

Answer: Using the QlikView Where exists() function, you can apply the FieldNumber function. If it results zero, the field is not present.

3. How to check if the field (column) exists or not in a load script?

Answer: You can try this code below which should work for you:

Inline Table:

LOAD*INLINE[
Field_1, Field_2
1,1
2,3
2,4
];
Let X = FieldNumber(Field_1,’InlineTable’);

If FieldNumber(Field_1,’InlineTable’)>=0 then
DupeTable:
Load
Field_1 as NewField_1,
Field_2 as NewField_2
Resident InlineTable;
End if

Conclusion

• Like many functions introduced in QlikView, this QlikView where exists function has its own importance as it works with the load function to provide the result if it exists.
• This function is useful to determine the table field value in the data load script if it has been loaded formerly into the field. The return type is Boolean so you may get the results as TRUE or FALSE and also is applied in the WHERE clause of an IF statement or, a LOAD statement.

Recommended Articles

This is a guide to QlikView Where Exists. Here we discuss the Overviews, syntax, function, key takeaways, Frequently Asked Questions (FAQs). You may also have a look at the following articles to learn more –

1. QlikView SubField
2. QlikView Architecture
3. QlikView Concatenate
4. QlikView Left Join