Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
Jennell_McIntire
Employee
Employee

Today I thought I would share how I used a combination of string functions and the FileName function to create data for my app.  I loaded several Excel files with one Load script and generated the data I needed for my app using the filename of the files.  Below is a subset of the files I was working with.  I had a separate Excel file for each cause of death and each gender.

files.png

Here is the script I used to load the Excel files:

script.png

I used a Crosstable Load to load the Excel files.  You can learn more about the Crosstable Load in Henric Cronström’s blog.  The Excel files include the country, the year and the number of people who died from the specified cause of death.  In my app, I also wanted to include gender and cause of death which was not included in the Excel file.  To do this, I decided to parse the gender and cause of death from the name of the Excel file.  Starting with gender, you can see in the files listed above that each filename ended with male or female.  In the script below, the Filename function returned the name of the Excel file including the extension but excluding the path (e.g. Death due to accidents - female.xls).  I use the SubStringCount function to determine if the filename included ‘- male’ or ‘- female.’  If it did, the function returned the number of occurrences which in this case would always be 1.  If an occurrence of ‘- male’ was found, then ‘Male’ was added to the Gender field.  If an occurrence of ‘- female’ was found, then ‘Female’ was added to the Gender field.

gender.png

The next bit of data I wanted to extract from the filename was the cause of death (see script below).  Each cause of death started at the 14th position/character so I used the Mid function to grab the text starting with the 14 character and I used the Index function to find the starting position of the hyphen so I could determine how many characters the Mid function needed to capture.  To figure out how many characters the Mid function should grab, I subtracted 15 from the position returned by the Index function (where the hyphen is located).  So if we look at the file named Death due to accidents - female.xls, the Mid function would start at the “a” in accidents and would grab 9 characters (the result of 25 – 14).  To finish it up, I used the Capitalize function to capitalize the first letter in each word of the cause of death.

cod.png

Since the files were all named and formatted the same way, I decided to minimize my script and use one Load statement to load all the files.  I could do this by using an asterisk (*) in my From clause like this:

from.png

This script loaded all xls files in the folder that start with “Death due to “.  Of course, I could have opted to load each file individually but why create more script to maintain.

 

Below is a sampling of the data I ended up with after loading the Excel files.  I can see the number of males and females (per 100,000 inhabitants) that died from an accident for each country in 2013.

table.png

In this blog, I reviewed a few helpful tips and functions that can be used in your script.  To recap, I used the FileName function to get the name of the file that I loaded and several string functions including SubStringCount, Mid, Index and Capitalize to generate the data for two new fields: Gender and Cause of Death.  I also discussed using the asterisk (*) in a single Load statement when loading multiple files that are named and formatted the same way.  This approach saved me time because it provided an easy way to create the data for the Gender and Cause of Death fields and there was less script to prepare.  Hopefully, you can make use of these functions in your app.

 

You can learn more about some of the string functions mentioned in this blog here.

 

Thanks,

Jennell

3 Comments