MAPPING Multiple Fields from Multiple Tables

I recently had to work on the SAGE X3 ERP and anyone familiar with this system will know just how many common tables exist. When creating a dashboard, we really don't want to display 1's and 0's or some other kind of integer key in a list box unless it actually means something to business. 

For this requirement, there were over 70 common tables and I needed to figure a way to map all these fields quickly, efficiently, dynamically and also find a way to manage these mappings in a single place. The below script allows multiple mapping to take place.

Here are the steps and script to use

1. Create an inline load with the list of tables
1. If a table needs to be used more then once, then use a delimiter like '_' to separate the menu items
2. Create a final mapping table by removing the delimiter
3. This step is only required if you have a single translations table where all common tables are listed, if not, you can skip this step
4. Create a 'for loop' to iterate through each common table with the function MAP USING to give our field information a more descriptive meaning to the users

String Functions: Part 2

It's been a while since my last posting; this is what happens when life and work priorities inundate us. None-the-less, I am excited to continue this series and explore string functions in greater detail.

I've always believed that the greatest challenge for any developer is being able to apply ingenuity with pre-built functions. Taking the shortest route possible to an answer and in the end, creating a clean and eloquent solution in its approach and performance.

Today we will be looking at Subfield(), a much loved function in the QlikCommunity

Subfield(s, 'delimiter' [ , index ]) - The help file explains this best :)

In its three-parameter version, this script function returns a given substring from a larger string s with delimiter 'delimiter'. 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 s. 

It is a very powerful function that allows you to either create a record for every instance encountered through the delimiter (Cartesian Join) or a sub-string when a particular instance is indicated through the index parameter.

Lets look at both its 2 and 3 parameter output

The 3 parameter output gives us our Full Name split into First Name and Surname. This allows us on the front-end to provide the end user with the option of now selecting how they want to find the person in question. 

A short-cut to this without scripting is utilising the 'tree-view' in the list box properties.Tree view based on field FullName using ',' as delimiter

2 parameter output gives us the Cartesian join on our field FullName.

The subfield function can be used at different stages within your load script. For instance, you can use it in your where clause to load a reduced dataset based on a condition that is met with your subfield() expression.

Happy Qliking


Cheers,
Byron

String Functions: Part 1

In this series, I will be exploring string functions. QlikView has an abundance of functions available at your disposal to manipulate and parse what is presented in the final data model. This series will be focusing on string functions used during the load process.

The series will explore the following string functions and their uses

• Left(), Right() and Mid()
• Index()
• Subfield()
• KeepChar() and PurgeChar()
• FirstSortedValue() and Min()
• TextBetween()
• Replace()
• FindOneOf()
• SubStringCount()

Part 1: Left(), Right(), Mid() and Index()

I am confident that every QlikView developer has used the above function in some way or another. Firstly, lets explore their use in a non-nested way

Left/Right(Text,Count) - Takes the input text whether a literal string or an expression and from the left or right side, returns the characters for that length. There are many situations where this is useful. One common use is when a field contains a value that can be described as the sub type and you want to extract this out

Even though the functions Replace() and PurgeChar() will be discussed in more detail in a later series; I wanted to illustrate how they can be used in conjunction with Left()/Right() to display certain values. The user can now select an ID category and then an ID number to further drill down and isolate

Mid()/Index() are very powerful functions, especially when used together. Mid(Text,Start,Count) is a 3 parameter function. The difference to left and mid is that you can specify where to start from. This adds a nice dynamic when used in conjunction with Index. Index(Text,Substring,Count) returns the starting position of a string within a string. Lets look at the following example which will illustrate how these 2 functions operate

Once again, for illustration I have used a function to be discussed later i.e. FindOneOf(). But as before, this is to demonstrate the versatility of the mid function in conjunction with any other string parsing expression.

My expression Mid(ID,Index(Upper(ID),'H',1),3) as HATSubType returned the value HAT for whenever that instance occurred. I used Upper() to ensure that the string I was matching to would be found, this is because the string to match is case sensitive. The output to the load above is

Essentially, 1 of 2 things need to be present when applying transformation to your data.

1. Does a pattern or set sequence exist - In the above examples, the subtype was always 3 characters long. Where it started did not matter now, what did was finding it. Since I knew the preceding character would be numeric, it was easy to find the starting position of my ID SubType
2. Is there a common delimiter - In the following example '011-254-7398', there is a '-' between each part in the above phone number. Since this will occur for all phone numbers, we can use Index(PhoneNumber,'-',which instance to use) to isolate the starting position of the characters we do want

I hope you enjoyed this part of the series and if you have any suggestions on what I should discuss next, please let me know

Until next time, cheers!

User Defined Measurements

Let's imagine this is your situation; you go to a client meeting where there is an initial scoping session for a proposed QlikView dashboard solution. You quickly identify that the client has preconceptions of what is possible from a reporting perspective and so has defined his charts and visuals based on what he is aware of. You also gauge through follow-up sessions that each of the functional heads for the different LOB want to see something slightly different.

One of the hardest challenges when designing a dashboard is deciding what charts to use and the layout to address all of the stakeholders requirements. One feature that I really like is dollar-sign expansions $(=string/variable). With some careful planning in your script, you are now able to allow your users to define their charting based on their requirements. 

This is a 5 stage approach

1) Name and define expression variable in script

2) Create an INLINE LOAD with the variable name and alias this variable through another column

3) Create Input boxes that will house your variable list - My expression in the listed values selection is ','&concat(DISTINCT AgentMeasureName,','). I also created an input box that will house my dimensions that I want the user to aggregate by. The values are simply a delimited list i.e. Calendar Date;Calendar Week;Calendar Month;Calendar Week Day;Team Leader;Manager;Shift

4) Create a bar chart with a dimension and expression that allows for dynamic reporting

The dimension I used was the following: 

I like my users to see a narrative that is clean and easy to read, hence the string match and what dimension to use. What's very important is that the variable that contains the drop down be used to match against and pick the relevant field to act as the dimension

5) Expression

What's great about this expression is that it will dynamically select the expression you defined in your script based on the selections users make on the front-end. The FieldValue(FieldIndex()) returns a string that contains the script variable name i.e. vAgentCallsAnswered. Since this is defined in our let statement, using a dollar sign expansion = $(vAgentCallsAnswered) returns our expression i.e. num(sum(AgentCallsAnswered)). The next dollar sign expansion then forces this to calculate and thus render the chart with the correct output based on selections made.

This approach can have many uses. One of my other favourite is for comments. No longer do i need to have a separate tab with a show/hide event to populate comments as text boxes. I can now have 1 text box in my main sheet and call the comment based values chosen by the EU.

Welcome to QlikVieW Ramblings!

As you might of guessed, this blog is mainly dedicated to QlikView. I will be posting interesting uses of functions and expressions and what I deem to be best practise given a particular situation.

The ramblings part is anything I feel is worth-while sharing. Whether it be around how we can be more giving in our daily lives / philanthropy, or some really cool visual techniques and mathematical algorithms. 

So who am I and what do I intend to achieve with this blog

I am a QlikView Consultant specialising in, guess what, QlikView :)solutions. I am a strong advocate of the product and believe that QlikView has changed the way we as consumers interact and visualise our data. It has also empowered anyone in the organisation to take charge and understand the drivers in their business. Allowing anyone to be a decision enabler to the decision makers.

Image Above: QlikView Open Data Challenge Winning Dashboard

The QlikCommunity Website is a great repository of feedback from experienced developers and urge all to go and register and participate in the discussions. This blog will have snippets of use cases around the most commonly used functions and expressions. I look forward to hearing your feedback on my posts and will always endeavour to address each and every question.

Happy Qliking and lets enjoy this journey of discovery together

Byron Van Wyk

QlikView Consultant

Inter-Active Technologies