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 !!

Concat() in QlikView

I'm happy to write a series of blog about useful String functions in QlikView in Detail. As a Start we will have a look at Concat() function and how this can be used in Real Life Scenarios. Before that if you didn't get a chance to have a look on my previous Post on one of the QlikView function Dual(), You may have a look at here.

When i started my Career with QlikView, I was not much aware about Concat() function but when i got introduced to it, i have started using it very frequently in one or the other way. This function can be used in both Chart as well as Backend Scripting. Output of the Concat() function is always String value which Concatenate all the value in the Expression over the Specified dimensions.

Syntax of the Concat() Function as follows.

concat ( [ distinct ] expression [, delimiter [, sort-weight]] )

Expression   -  Field or Expression to be concatenated.

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

Sort-Weight  - The order of concatenation may be determined by sort-weight.Sort-weight should return a numeric value where the lowest value will render the item to be sorted first.

Let's see with the Example, how this Concat() function work, Im taking a simple data with inline table to Explain this.

I have Added  Inline table  and reloaded the Dashboard. Below are the three expressions by adding Parameters.

1. Concat(TestConCate)    - Result of the Expression is ABCD

2. Concat(TestConCate,',') - Result of the Expression is Delimited by Comma  A,B,C,D

3. Concat(TestConCate,',',SortOrder)  -  Result of the Expression is Sorted by 3rd Parameter D,C,B,A

Let's have a look at some of the ways concat() function can be used in qlikView with few scenario`s

Scenario 1: Concatenate Multiple Rows into Single Row Based on the Specified Fields.

For Example your are working with a company who manufactures LAPTOP. Laptop which has been sold to end customer has some defect and it has been returned to your company from end customer. So to provide a replacement for the end customer, this defective laptop has to go for Many internal approvals. So in ERP tables, this may be stored as one laptop name with multiple records for each department approval.

Let's assume that our data in Database looks as below.

Let's take this data for our analysis. Laptop A Got into 5 different approvals but Laptop B has got into only 3 approvals. Now your user Wanted a report in qlikview which concatenates all the approvers records by Distinct laptop into single row. so Expected result from the above data is 2 records.

You can Achieve this Scenario by Multiple way  one way is using Previous() and Peek() functions but getting this is bit Difficult and time consuming with these functions. Easiest and very simple way would be using concat() function.  Just using the Field name with Delimiter and Group by function will Do the Magic. Sample Code Should look like the below one.

And  Output is

It is just simple as that to concatenate multiple rows into single row in QlikView :)

Scenario 2: Concat() in Set Analysis with Island Table.

For Example you are getting requirement from customer that you'll have to restrict data for a report (Not from the Complete data model)  using the data from Excel file which does not have any link to your existing data model. Yes, We will look for optimized and robust Solution get the work done and this can be achieved using many ways, like Set Analysis using Concat() function, or using applymap() function from backend by  creating Flag,  or Combination of Set analysis using WildMatch function at frontend chart.  Let's have a look on few of them now.

I`m going to take the same Work flow data what i have used above for this as well with additional Excel file which i have Received from user (in this case i'm loading the data using INLINE Table itself) as shown  below. Information inside Field IGNORE_FROM_REPORT has to be used to ignore it from report. We have Got AUDITOR and MANAGER information is available in this igone field, So user don't want to see the approval flow from Auditor and Manager in report.

Once i reloaded my Dashboard, I`ll have two Seperate table Without any link.

If you have island table in QlikView without any link to your Data Model, you would not be able to use this island table field to equate it to your datamodel field in Set Analysis. Below Expression would not work and Fails & gives you all the data.  So don't think to Equate the Field Directly in Set Analysis like DATAMODEL_FIELD = ISLAND_TABLE_FIELD which would always gives you wrong data untill unless you have correct link.  

Count ({<Approvers -= {IGNORE_FROM_REPORT}>} Approvers) // Always Produces Wrong Data

So in this case Concat() function comes in Handy With Set Analysis. To Equate the String field in Set Analysis i need my value to be single quoted and comma separated. So i'm going to form a expression using Concat() function which can be used inside the set analysis to get the expected result. I would need something like 'AUDITOR','MANAGER' to use it in set analysis,

so let me Start forming the Expression Step by Step. Concat() function with Delimiter as comma would give me result as AUDITOR,MANAGER

Concat(IGNORE_FROM_REPORT,',')

But i would need single Quote to be concatenated before and After the each value, so i`m going to use Chr(39) function to concatenate the Single Quote. 39 is ASCII value for Comma.

chr(39)&Concat(IGNORE_FROM_REPORT,chr(39)&','&chr(39))&chr(39)

Now i got  the Expected string out of the above Expression ('AUDITOR','MANAGER' ) which i can use inside Set analysis .So this Expression can be used in set analysis as show below directly or using the Match/WildMatch functions.

count({<Approvers = {$(=chr(39)&Concat(IGNORE_FROM_REPORT,chr(39)&','&chr(39))&chr(39))}>} Approvers)

count({<Approvers = {"=Wildmatch(Approvers,$(=chr(39)&Concat(IGNORE_FROM_REPORT,chr(39)&','&chr(39))&chr(39)))>0"}>} Approvers)

First Expression Column in above table is producing wrong result as we have used the ISLAND FIELD directly in our data model without any link. Second and Third expressions are  producing Expected result as we are treating the ISLAND field as String Value and Equating them back in our Data Model.

Hope this gives you a picture of how Concat() function can be used with different functions and Set Analysis.

Thanks for reading the post and Happy Qliking !!!

Partial Reload in QlikView

Thanks For Stopping by and reading this post !!.

Partial Reload is the powerful feature in QlikView which help to add / replace data to QlikView Applications without reloading the complete application.

Usually When We do a full reload in QlikView application, QlikView drops all the tables from Memory and recreates the Table again from the Script But with Partial reload, we can reload only certain load/Select statement by Keeping other tables in QlikVeiw Memory. In other words When we use partial reload feature, “Load/Select/Map..Using” statements with Add/Replace only get Executed and all other remaining tables in QlikView Memory is retained.

There are two KeyWords by which partial reload can be activated in QlikView Scripting.

1. Add       - Append Data to Existing Table or Add New Table to your Data Model
2. Replace - Replace Existing Table.

Above Keywords can be prefixed only with below statement.

1. Load
2. Select
3. Map...Using

Partial Reload can be triggered from File >> Partial Reload (Ctrl + Shift + R)

 

Partial Reload - Add Keyword :

ADD Keyword prefixed with “Load/Select/Map..Using” helps to append the data to existing table. And also it just does the blind append, it will not check for any Duplicate values.

For Example, i'm loading the simple month data with one field in my QlikView applications to demonstrate this Feature.

Loaded 3 months of data and done full refresh. My Data in data model looks as below.

Now i would like to add few more months without reloading my old data. For this i could use ‘ADD’ keyword with partial reload.

i have added  inline table with APR and MAY month with ADD keyword and done partial reload, So my month column has got a new records now.

To prove that QlikView will retain the tables without Add / Replace Keyword in Memory and Execute the only the Statement which has ADD/REPLCE Keyword, i have commented my 1st statement and Ran the Script again using Partial Reload. Below is the Output.

You could see the o/p above, it does added the New record to my existing table without dropping it. Also You could have spotted that Apr and May has been added twice as previous datamodel  had APR and MAY. This also proves that ADD Keyword would not check for any duplicates. it simply adds the records to Existing tables. If i rerun my script again, you would see APR and MAY Month added one more time and Frequency turns to 3.

This Can be Avoided using ‘Not Exist’  in Where clause or using Load ‘Distinct’ Table.

You would see the Effect of ADD keyword only with Partial Reload. With Full reload, QlikView Drops all the tables which are in Memory and loads it again so adding ADD Keyword is as good as doing  Straight Concatenation.

We could also force QlikView not to execute ADD Statement while doing full reload using ONLY Key word.

ADD ONLY Load …. From Table; (This would get executed only with partial reload)

Partial Reload - Replace Keyword :

REPLACE Keyword prefixed with “Load/Select/Map..Using” helps to replace the data if the table is already existing.  With the above Example, im planning to Replace JAN, FEB, MAR INLINE table with APR and MAY Inline Data. When I do a Partial Reload, QlikView Reads my Load statement which has REPLACE Keyword and see`s if the table with the same name exist and replaces it.

Step 1: QlikView Loads the Data from 1st Load Statement ( From Line No 13 to 19)

Step 2: QlikView Loads the Data from 2nd Load Statement ( From Line No 22 to 26) and Replaces the 1st load as the table Name is same.

This Replace statement Works with both Full Reload and Partial Reload.  This can be also forced to run only during the Partial Reload using ‘ONLY’ Keyword.

Partial Reload can be triggered from Command line using Batch file as below using ‘/rp’ or ‘/lp’

[ path ] Qv.exe [ { ‘/rp’ or ‘/lp’ } documentfile ]

Scenario	Add	Replace
What it does?	Appends the Data to the Existing Table	Drop Existing Table and Full Load
Concepts Works With?	Load / Select / Map..Using	Load / Select / Map..Using
Executed During Normal Reload?	Yes	Yes
Meaningful during Normal reloads?	No	Yes
Meaningful during partial reloads?	Yes	Yes
Check for duplicates is performed?	No, Appends the Data Without Checking for Duplicates	Drop Existing Table and Full Load (No Need of Duplicate Check)

QV Statements	Full Reload	Partial Reload
Load A, B From Table;	Drops Table and Full Load	Nothing
Add Load A, B From Table;	Uppend Data to Existing Table	Uppend Data to Existing Table
Add Only Load A, B From Table;	Nothing	Uppend Data to Existing Table
Replace Load A, B From Table;	Drop Table and Full Load	Drop Table and Full Load
Replace Only Load A, B From Table;	Nothing	Drop Table and Full Load