EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials QlikView Tutorial QlikView SubField
Secondary Sidebar
QlikView Tutorial
  • Basics
    • What is QlikView
    • QlikView Architecture
    • Qlikview Dashboard
    • QlikView Functions
    • Peek Function in QlikView
    • QlikView Tools
    • QlikView Set Analysis
    • QlikView Charts
    • QlikView Concatenate
    • QlikView Left Join
    • QlikView Interview Questions
    • QlikView SubField
    • QlikView ApplyMap
    • QlikView Aggr

QlikView SubField

QlikView SubField

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.
Note: The function can be applied in place of implementing complex functions combinations using Right(), Len(), Mid(), Left() or several other string functions.

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.

QlikView SubField 1

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

QlikView SubField 2

2. Opening the Edit Script

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

Opening the Edit Script

3. Clicking on the Table Files

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

Clicking on the Table Files

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.

QlikView SubField 5

5. Loading Code of Edit Script

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

Loading Code of Edit Script

6. Saving the File

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

QlikView SubField 7

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.

Visiting Sheet Property Window

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.

Script applying the Subfield Function

QlikView SubField 10

QlikView SubField 11

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.

user can view that the function removes a string

QlikView SubField 13

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:

Name First_Name Last_Name
John Collin John Collin
Mary Owen Mary Owen

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:

Performer Task
Ram OST
Ram Music
Krishna Video
Shiva OST
Shiva Video
Shiva Music

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
Popular Course in this category
QlikView Training (2 Courses, 1 Project)
  2 Online Courses |  1 Hands-on Project |  9+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Business Intelligence Training (12 Courses, 6+ Projects)4.9
Business Analysis Training - Planning Monitoring and Strategy Analysis (12 Courses, 8 Case Study/Project)4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more