SubField() Function in QlikView Application: Part 1

The Subfield function

The Subfield function is the most loving function of QlikView because it automatically removes duplicate records generated by the function (Load and DISTINCT statement). This function lies between the string and text function category. This type of function can be used in the script and UI. The Subfield function returns the substring from the string or variable. In the Subfield function we use negative numbers to extract the string from the right and side. By default we use positive numbers to extract strings.

The following is the syntax of the Subfield function:

str Subfield (text, delimiter [, field_no])

There are three parameters.

str: The first parameter is a string. In this parameter we do search operations. This script function returns a given substring from a larger string.

Delimiter: The second parameter is a string. This parameter breaks the string (first parameter).

The third parameter is the position of the result of the string from the search.

Use of Subfield function

The following are the uses of the Subfield function:

• Using the subfield function, we avoid complicated usage of functions like Len(), Right(), left() and so on.
  
• Multiple subfield functions can be used in a single load statement.
  
• It is a very useful function when working with strings along with the '$' expansion.

How to use Subfield function in QlikView

There are some basic procedures for working with the Subfield function in QlikView.

Step 1: Open QlikView application

In the first step you need to open the QlikView application then go to File-> New, then this window will be opened.



Step 2: Open Edit Script

The second step is to open the edit script window from, File-> Edit Script.



Then this window will be opened.

Step 3: Click on table files.

In this step we import our Excel file and click on the Open button.

Step 4: File wizard type.

Then this window will be opened. Here you can see your Excel file table and click on the Finish button.



Step 5: Code of edit script.

Now, in the edit script the Excel file is uploaded successfully and reload it.