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.

Table1:
LOAD [% of world population],
     [Country (or dependent territory)],
     Date,
     Population,
     Rank,
     Source
FROM
[\\MCNSERVER2\UserProfiles\ptiwari\Desktop\Excel file.xls]
(biff, embedded labels, table is Sheet1$);

Step 6: Save file.

The next step is to save our QlikView file.



Step 7: Sheet property window.

The sheet property window will be opened. In this window you add fields that you want to display as a table and click on the OK button.



Now you will see this window.



Step 8: Apply the subfield function in the edit script

The next step is to open the edit script window again and apply the subfield function and reload it.

Table1:
LOAD [% of world population],
     [Country (or dependent territory)],
     Date,
     Population,
     Rank,
     Source
FROM
[\\MCNSERVER2\UserProfiles\ptiwari\Desktop\Excel file.xls]
(biff, embedded labels, table is Sheet1$);

Table2:
LOAD [% of world population],
     [Country (or dependent territory)],
     Date,
     Population,
     Rank,
     Source,
SubField (Rank,',') As Ranks,
SubField(Source,',')As Sources

Resident Table1;
DROP Table Table1;

Here you can see in this example I apply the Subfield function in two fields, like Rank and Source. Now see after applying the subfield function what happens. Such as it is very interesting!

Then sheet property window will be opened, here two new fields are generated then add these fields.



Then you will see two more fields showing in the main window, in other words Ranks and Sources.



Step 9: If we apply a negative number in the subfield function then you will see the subfield function extracts a string from the right and side.

Table1:
LOAD [% of world population],
     [Country (or dependent territory)],
     Date,
     Population,
     Rank,
     Source
FROM
[\\MCNSERVER2\UserProfiles\ptiwari\Desktop\Excel file.xls]
(biff, embedded labels, table is Sheet1$);
Resident Table1;
DROP Table Table1;

Table2:
LOAD [% of world population],
     [Country (or dependent territory)],
     Date,
     Population,
     Rank,
     Source,
SubField (Rank,'',-2) As Ranks,
SubField(Source,'',-3)As Sources

Here you can see in this example I pass (-2) to the Ranks field and (-3) Sources field then the subfield function extracts a string from the right hand side and shows a different string.



Summary

This article describes the subfield function in QlikView and also describes why it is used in applications.