Cooking with Qlikview Variables

So it's six days since my first post and here we are at 350 views!


I was originally planning to cover something more basic in this post but I had an idea earlier on this week that I thought would be a cool demo and after trying things out I think I have it.


This post is called "Cooking with Qlikview Variables", I will be using variables input on screen in order to generate data that is then used within the Qlikview document itself...

The Finished Product - Cooking with Qlikview

You can click on these images to view them original size

So here it is; I've gone easy on the charts for this one as in this case we’re looking at how to create the data.  This document was written in QV11 and some of the functions may not be available in previous versions.

http://community.qlikview.com/qlikviews/1252

Note: after a few people have raised some issues with access I have also made this Qlikview available here

Fairly standard Qlikview fare you say, bring something interesting to the table…

Nothing odd going on here

Well let’s look at those suspicious looking input boxes at the side, there is an awful lot of them.

This document is fully interactive and allows you to input a small amount of data which it then loads into a table.  In order to use this Qlikview document you need some recipe data, I've taken mine from the below:

http://www.bbc.co.uk/food/recipes/cheesecake_brownies_01372

I made these brownies last week and they were so good my wife forced me to take them to work before she ate them all.  Looking at the stats coming out, I think that was a wise decision.

What is an Input Box and creating one

An input box allows you to free type variables on the sheet to be used elsewhere.  To create an input box, right click on the sheet

> New Sheet Object

 > Input Box.

Here you can choose whether to amend existing variables in the document or to create new ones.  Using the label function allows us to set a user friendly name which we may not want to use in the background.

Hungry yet?

#Protip: use a unique identifier to prefix your variable name such as vUser to avoid confusion with normal data.

I've split my inputs into sensible boxes, first is the Name of Meal and number of Portions, followed by the list of ingredients, the amounts used and the recipe steps.

You then have to do a bit of research and find the amount of sugar and fat per 100g/ml (I have assumed a millilitre is equal to a gram).

Then click the button Set Charts.  This has an action of reloading the document.

The Script

The First thing I did was change the money format as I wanted to repurpose it to display weights.

My code is always perfect annotated ...honest

I then use an inline Load script that is usually used to load in flat data and this inputs the updated variables into the table.  I have used the $(variable) as this forces Qlikview to calculate the variable instead of treating the values as flat text.

I then perform some calculations to work out the per portion amount of Fat and Sugar.

If I was happy with a per portion figure we could stop here… but who can only have one piece of brownie?

AutoGenerate a row for each number

 On the next tab, I use the AutoGenerate function to create a table with an entry for each portion. I then joined this to existing nutrition table so that every portion is broken down.

I’ve then added some very quick data so that you can compare your recipe with some standard foods, a big mac, a ‘healthy’ subway and a biggish salad.

I then drop all the tables I don’t need and clear out the variables I have used in the query.

If you load your own recipe, all the chart captions will reflect this (I hope I didn’t miss any!).

The first chart highlights how your recipe compares to my ‘standard’ foods; the brownies are definitely not looking that healthy!

The other charts show how much sugar and fat make up each portion, ingredient and banding of each ingredient by their junk%.

I’ve also included a Table of all data, which I wouldn’t normally do but gives you lovely people an idea of how it looks.

But wait, that’s not all…

Let’s click the Lets Cook $(MealName) button which takes us to the second tab.

We loaded up the steps in the recipe and this tab displays this in nice big font (say if you’re cooking and have the recipe on your laptop and want to be able to read it clearly)

The next step button set the variable CurrentStep to $(NextStep) which sets the initial value to 1 and increases the value by 1 for each click.

The Text box displays the corresponding variable dependant on the CurrentStep value (I’m not happy with this coding so if anyone has any ideas get in touch!) If the Step Text is blank, then it shows Finished.

That awful moment when you realise you only coded step 7....

Clicking the Finished Button, sets the CurrentStep value back to 0 and takes you back to the first tab.

I could have included a previous step button but I think you should be able to work it out from the NextStep variable.

Real world applications

Whilst this was a fun little project, I think it has some real world applications (although maybe not for recipes) Having users set their own values into the data can be useful for decision making and could be incorporated into What-if analysis, for example budget setting.

Links

1. Today's Qlikview (Qlikview Community)
2. All my published Qlikviews (Qlikview Community)

Edit: after a few people have raised issues with access I have also made these Qlikview docs available on Google Drive

3. Today's Qlikview (Google Drive)
4. All my published Qlikviews (Google Drive)