SubField() Function in QlikView Application: Part 1

This article explains what Subfield function in QlikView is.

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.

After clicking New option

Step 2: Open Edit Script

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

window of edit script

Then this window will be opened.

window

Step 3: Click on table files.

In this step we import our Excel file and click on the Open button.
table files
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.

file wizard

Step 5: Code of edit script.

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

reload

  1. Table1:  
  2. LOAD [% of world population],   
  3.      [Country (or dependent territory)],   
  4.      Date,   
  5.      Population,   
  6.      Rank,   
  7.      Source  
  8. FROM  
  9. [\\MCNSERVER2\UserProfiles\ptiwari\Desktop\Excel file.xls]  
  10. (biff, embedded labels, table is Sheet1$);  
Step 6: Save file.

The next step is to save our QlikView file.

save 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.

sheet

Now you will see this window.

main

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.

reload1
  1. Table1:  
  2. LOAD [% of world population],   
  3.      [Country (or dependent territory)],   
  4.      Date,   
  5.      Population,   
  6.      Rank,   
  7.      Source  
  8. FROM  
  9. [\\MCNSERVER2\UserProfiles\ptiwari\Desktop\Excel file.xls]  
  10. (biff, embedded labels, table is Sheet1$);  
  11.   
  12. Table2:  
  13. LOAD [% of world population],   
  14.      [Country (or dependent territory)],   
  15.      Date,   
  16.      Population,   
  17.      Rank,   
  18.      Source,     
  19. SubField (Rank,',') As Ranks,  
  20. SubField(Source,',')As Sources  
  21.   
  22. Resident Table1;  
  23. 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.

sheet1

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

main1

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.

reload2
  1. Table1:  
  2. LOAD [% of world population],   
  3.      [Country (or dependent territory)],   
  4.      Date,   
  5.      Population,   
  6.      Rank,   
  7.      Source  
  8. FROM  
  9. [\\MCNSERVER2\UserProfiles\ptiwari\Desktop\Excel file.xls]  
  10. (biff, embedded labels, table is Sheet1$);  
  11. Resident Table1;  
  12. DROP Table Table1;  
  13.   
  14. Table2:  
  15. LOAD [% of world population],   
  16.      [Country (or dependent territory)],   
  17.      Date,   
  18.      Population,   
  19.      Rank,   
  20.      Source,     
  21. SubField (Rank,'',-2) As Ranks,  
  22. 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.

main2

Summary

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