QlikView SubField

Updated March 15, 2023

Introduction to QlikView SubField

The subfield function of QlikView is considered one of the most adoring ones because this function helps to eradicate duplicate data records automatically from the database tables produced by the function with Distinct and Load query statement. Since lying between the text and string category of function, this subfield() function in QlikView is implemented in the UI and script. The QlikView function provides a substring due to the variable or string. By default, we need to use a positive string for extracting strings, but in this QlikView subfield function, we need to apply negative numbers for extracting the string positioned from the right and the side.

Using QlikView SubField Function

We have the following syntax of the function as follows:

Subfield(text(or, expression), delimiter [,field_no.(or, index))

The parameters included in the above syntax are given in brief:

The first argument is “text or say expression”, that is a string that is used for the search operations. It can be in the form of a variable, a hard-coded text, a dollar-sign expansion, and even any other expression. The subfield function is a script function that returns a specified substring when executed from a bigger string.

The next parameter or argument is “delimiter”, which is characterized inside the input text separating the string into module parts.

The last one is the “field_no. or say index”, which is an optional argument holding an integer value that identifies the substrings of the parental string text to be returned.

It defines as:

• If the field_no. has positive value, then the substrings on execution are removed from left to right.
• If the field_no. has negative value, then the substrings on execution are removed from right to left.

Let us see how to use this subfield function working with basic methods in QlikView:

1. Opening the QlikView Application

Initially, one needs to open the application QlikView.

After that, you can go to the option File, then New, and a window will be opened.

2. Opening the Edit Script

From the same File option, secondly, you need to open the script window by clicking the Edit Script option.

3. Clicking on the Table Files

Here, the user will import the Excel file, and the Open button needs to be clicked.

4. Visiting File Wizard Type

After importing, this window will be opened where you can view the Excel file table. Just click on the Finish button.

5. Loading Code of Edit Script

Finally, the Excel file uploading has been completed successfully in the edit script. Reload it as.

6. Saving the File

This is an important step to save the QlikView file created as.

7. Visiting Sheet Property Window

• Now, the sheet property window is opened, where fields that need to be displayed using a table can be added. Then the OK button should be clicked.

8. In the Edit Script, applying the Subfield Function

Again, you need to open the edit script window to implement the subfield function; after that, reload it again.

9. When a negative number is provided in the subfield function in QlikView, then the user can view that the function removes a string positioned from right and side.

QlikView SubField Load

• QlikView consists of several String functions that support transforming and loading the data specified into the QlikView, which helps clean the data.
• The subfield function can be applied either at the frontend or backend and performs just opposite to the Concat function. But the concat() concatenates the records provided, whereas the subfield() function splits the records based on delimiter and index considered parameters.
• The function is used to remove substring parts, suppose firstname and lastname from the parent string, which can be a list of records having full names, module parts of a path name, otherwise can be fields data from tables separated by comma or a delimiter.
• If this function is used in a LOAD statement where it consists of an optional argument field_no being left out (i.e., if index or field_no is omitted), then a complete record is produced for every substring found in the field. But when multiple fields using the subfield() function are loaded, it creates the Cartesian products of all arrangements.

For illustrations, let us consider a few examples as follows:

Code:

Subfield(‘xyz;ghj;uio’, ‘;’,2) – it returns ‘ghj’
Subfield(‘’, ‘;’,1) – it returns NULL
Subfield(‘;’, ‘;’,1) – it outputs an empty string

Now, you can add the example script to your document and execute it. After that, you can add, the listed fields to a sheet provided in the results column in your document to view the output.

Code:

Full_Name:
LOAD * inline [
Name
‘John Collin’
‘Mary Owen’
];
Sep_Names:
Load Name,
Subfield(Name, ‘ ‘, 1) as First_Name,
Subfield(Name, ‘ ‘, 1) as Last_Name
Resident Full_Name;
Drop table Last_Name;

Output:

Let us assume that we have a variable with a path named vDemoPath.

Code:

Set vDemoPath= \Users\ext_jrb\Documents\Qlik\Sense\Apps;.

The user can add a measure as follows in the text & image chart:

Subfield(vDemoPath, ‘\’, -3), this will result in ‘Qlik’. This happens because it will be the third substring from the end of the right hand of the vDemoPath variable.

Hence, the above example displays how several table rows can be created using only one instance of the QlikView subfield().

Suppose next we have another script to add to the document and execute it:

Code:

LOAD DISTINCT
Performer,
SubField(Task, ‘ ‘) as Task;
LOAD *inline[
Performer|Task
Ram|OST,Music
Krishna|Video
Shiva|OST,Video,Music
] (delimiter is ‘|’);

Output:

Hence, the above illustration displays the cartesian products of all arrangements created inside the same LOAD statement using several instances of the function where they hold no value provided for the optional field field_no or index. Here, the DISTINCT keyword helps to remove the replicates of the record data.

Conclusion

The function is used generally to avoid the complicated usage of functions such as Right(), Len(), Left(), etc. The user can implement various subfield functions executed in one load statement. This subfield function is very advantageous when we work with strings plus with expansion “$” type.

Recommended Articles

This is a guide to QlikView SubField. Here we discuss the introduction, using the QlikView SubField function and load, respectively. You can also go through our other related articles to learn more –

1. QlikView Architecture
2. QlikView Alternatives
3. QlikView Charts
4. QlikView Concatenate