Multiple Ways To Use Subfield() in QlikView

QlikView has many String function which helps us to Transform and loads the data into QlikView. We are going to see one such string function Subfield() in this post. In real world you may not always get a Clean data from Customer to develop a dashboard. Yes, road will not always same and  the customer data is also the same, so most of the cases you would need to cleanse up the data & Transform and Complete the dashboard, QlikView is kind of tool which has so many String functions and definitely which help us to Clean the data.

Subfield field is one such Function which can be used to transform and cleanse  your data, This function can be used at backend and also at frontend. This works Just exact opposite of Concat() function. Concat() function Concatenates the Data what is been provided but subfield Splits the data based on the Delimiter and Index provided as parameter. Subfield take three parameter to Process the data. Syntax of subfield as follow.

Subfield( Expression, 'delimiter' [ , index ] )

Expression    -  Field or Expression to be Subfield.

Delimiter        -  Each value may be separated by the string found in delimiter

Index               -  Index is an optional integer denoting which of the substrings should be

Returned. If index is omitted when subfield is used in a field expression in a load statement, the subfield function will cause the load statement to automatically generate one full record of input data for each substring that can be found in FIELD.

Below is the 1st level Example :

subfield(FIELD, ';' ,2) returns 'cde' if FIELD is 'abc;cde;efg'

subfield(FIELD, ':' ,1) returns NULL if FIELD is an empty string

subfield(FIELD, ':' ,1) returns an empty string if FIELD is ':'

Split Single Rows to Multiple Fields :

Most Common Scenario when this Function will be used is to convert the single row into the Multiple records, For Example in Some Transactional Systems due to the data Volume they concatenate multiple records into Single row based on the Common Key to store the data. But when it comes to Data warehouse, You would need this data to be stored it as multiple records for Reporting.

For Example Let's take telecom industry, when you get a mobile number from service provider, from their they track your activities for multiple Reasons, one such thing is to Calculate the Monthly Billing,  Since Telecom industry  creates the huge data and one customer can do many calls, so data in table will be huge, so from transactional system point of view application team want to concatenate multiple fields and store it into one field to optimize storage from infrastructure perspective  so they have concatenated the ‘Type of call’ and ‘Minutes’  of call made by customer into one column. The data in table looks like below.

CALL_TYPE_MINUTES Columns has been Concatenated from CALL_TYPE and NOS_MINTUSE.

Now  user want to create a Report which says CUSTOMER_ID, TRANSACTION_DATE, CALL_TYPE, NO_OF_MINUTES and AMOUNT for Each customers.

Amount can be Derived from the Rate Columns which says the Rate for Local and International Calls per Minute. Like Below,

Now we need to Transform the Transactional Data into Multiple Rows by Customer and Transaction Date to Get the Call_Type and Minutes Separately as shown below.

To get above output, im just going to add Subfield function with delimiter without the index in backend Since we need all the rows.  My code Looks Below to turn Single row  to multiple row.

Now i can use the KeepChar and Purgechar function to Split the Call_Type and Minutes to Separately  and lookup the Rate table to get the CallRate to Multiply the CallRate with Minute to get the Amount in my report.

Hope this helps!! Thanks for reading !!