topic Field Groups and Hierarchies in QlikView

Field Groups and Hierarchies

Fri, 04 Sep 2009 22:50:49 GMT

I am a new user using the Personal Edition of QV. I need help in creating an account hierarchy similar to those in OLAP tools such as Essbase or Analysis Services.

My data file has account information in multiple Fields (columns). I want to group these by P&L categories such as GrossProfit, Sales and SGA for example. My goal is to start at the highest level such as NetIncome and drill down through the Account Groups and Accounts that make up the hierarchy.

Any help would be appreciated, especially detailed step-by-step instructions to help out a novice! [:)]

Thanks

Field Groups and Hierarchies

johnw — Sat, 05 Sep 2009 04:41:03 GMT

There are several ways to handle hierarchical information. You can do a hiearchy load, but I haven't actually had a use for it yet, only played with it to understand it. What I much more typically do is create cyclic or drill down groups, and leave my data completely unhierarchical.

For example, I might have a table like this:

Group Division Department Account Value
Operations Rolling Roll Maint 12345 50000
Operations Rolling Machine 1 12367 40000
...

Even if Group, Division, Department and Account are a hierarchy, I'm likely to just read the table in flat like that.

But I might want a bar chart where I can drill down through the hierarchy. So then I would define a drill down group made of of Group, Division, Department and Account. I would use that group as the dimension on a chart. I'd use sum(Value) as the expression for the chart. The chart would then start off displaying the total by Division for the Group. If I click on a Division, it would show me the total by Department for that Division. And so on.

Or I could create a pivot table and put the four fields in as dimensions, sum(Value) is as an expresison, and ask for subtotals. I could then navigate through the hierarchy by expanding and collapsing nodes in my pivot table.

So in general, my approach to hierarchies isn't to build them into the data model, but simply to define them on the fly in the charts as I need them. In general, I feel that's a more flexible approach, particularly when the hierarchies aren't rigid, and are just how the users want to see it right now.

See attached example for how you could acutally implement everything I was saying above.

Field Groups and Hierarchies

Mon, 07 Sep 2009 17:49:22 GMT

Hi Vince,

As John already mentions: you could leave the different levels in your data as separate columns, and make a drill-down group in your user interface to use as a dimension in charts/graphs.

This assumes a balanced hierarchy: each path from the highest level (NetIncome) to the lowest level (Accounts) has the same number of steps. If you're using an unbalanced hierarchy (e.g. containing paths like 'Board of Trustees' -> 'Department of Education' -> 'DoE Staff' (3 steps) in one case, and 'Board of Trustees' -> 'Department of Computer Science' -> 'San Diego campus' -> 'DCS San Diego staff' (4 steps) in another case), QlikView has the 'hierarchy' and 'hierarchybelongsto' prefixes to the LOAD statement to transform your data.

Read pages 303 / 304 of the 'QlikView Reference Manual.pdf' in the QlikView installation directory (this is MUCH clearer than the QlikView help file) for an explanation.

Sorry that I can't offer a step-by-step, guess this depends too much on what you're trying to achieve.

With regards,
Martijn ter Schegget
CND Development

Field Groups and Hierarchies

Mon, 07 Sep 2009 19:08:31 GMT

Hi,

just one question when reading your post ...


John Witherspoon wrote:
See attached example for how you could acutally implement everything I was saying above. <div></div>

A user using the "personal edition" is not able to open your example, isn't it?

Best regards
Stefan WALTHER

Field Groups and Hierarchies

johnw — Tue, 08 Sep 2009 23:55:25 GMT

I'm not really up to date on what Personal Edition can and can't open. If it can't open example files from other people, that sounds like a problem. Or maybe the problem is on my end - I'm mostly using 8.5, but I do have 9.0 personal edition on my machine so that I can get some experience with it before we upgrade officially. Maybe I saved it under personal edition, and therefore others can't open it? I'm saving in 8.5 and reposting just in case that fixes it.

Field Groups and Hierarchies

Wed, 09 Sep 2009 20:05:42 GMT

Thanks for the posts but I can not open your files with my personal edition. I understand Groups as they pertain to dimensions in your first example. However, my data is not structured that way. Instead, I want to group Expressions together. Here is a simplified example of the file layout:

Date Customer Product Sales Rebates COGS Marketing Travel

2008.FEB WalMart Towels 12000 -200 -8000 -400 -100

2008.FEB WalMart Tires 19000 -500 -13000 -900 -500

I want to group similar accounts (they appear in the Expressions tab when I create charts) together such as Sales and Rebates grouped as NetSales, Marketing and Travel together as SG&A, NetSales and COGS as GrossProfit and GrossProfit and SG&A as NetIncome. . As this file shows, the group parent accounts do not exist. Having created groups, I wanted to then drill down into them starting at NetIncome all the way down to the the lowest account level details.

Any further advice is appreciated.

Field Groups and Hierarchies

Thu, 10 Sep 2009 15:03:13 GMT

Hi John,

Personal Edition files can't be opened with other people's Personal Edition QVs. Luckily, a Partner License version can open pretty much anything .

I took the liberty to copy the scripts and objects from your version into a new one; anyone should be able to open this one.

With regards,
Martijn

Field Groups and Hierarchies

Thu, 10 Sep 2009 16:22:54 GMT

Hi Vince,

I've played around with the example data in your last post (assuming the two rows you posted are your source data, and not the requested output format); see the attached QVW for the result. The issue is in trying to roll up the expressions (instead of dimensions); I've solved this by 'flattening' the data into a format with one row for each measure, with two columns: one showing which measure this is (Sales, Rebates, etc.) and the other showing the value. I've then built a hierarchy on the measures, and used this as a set of dimensions to group by in pivot tables.

By the way: this only works (i.e. is conceptually correct) if your measures are in the same unit (in this case some currency) and can be added together!

Hope this solves your problem.

With regards,
Martijn ter Schegget

PS: the data used when rolling up the expressions is remodelled to something like this:


2008.FEB, WalMart, Tires, Sales, 19000
2008.FEB, WalMart, Tires, Rebates, -500
etc.

Field Groups and Hierarchies

Sat, 12 Sep 2009 03:00:30 GMT

Martijn,

You understood my request perfectly. Unfortunately, I can not open your QVW.

My question then is did you transform the data within QlikView or did you transform it prior to loading into QlikView?

Thanks for the prompt and thorough responses.

Field Groups and Hierarchies

Mon, 14 Sep 2009 15:32:31 GMT

Hi Vince,

I've used the following code to transform the data:


LOAD
 Date,
 Customer,
 Product,
 pick(iterno(), 'Sales', 'Rebates', 'COGS', 'Marketing', 'Travel') as Measure,
 pick(iterno(), Sales, Rebates, COGS, Marketing, Travel) as Value
RESIDENT SalesData
WHILE iterno() <= 5;

This code loops 5 times over each row in the original data (once for each of Sales, Rebates, COGS, Marketing, Travel), and for each loop loads one of the values 'Sales', 'Rebates', etc. as Measure and the matching field value as Value.
(The iterno() function returns 1 for the first loop over a row, 2 for the second loop, etc.; it starts back at 1 for the first loop over the next row, etc. The pick() function looks at it's first parameter, and returns the first of the other parameters ('Sales') if it's 1, the second ('Rebates') if it's 2, etc.).

With regards,

Martijn

PS: It's a QV9 file; are you using 9 or 8.5 to open it?