topic How to build a hierarchy? in QlikView

How to build a hierarchy?

Thu, 13 Aug 2009 21:42:30 GMT

Hi,

It is propably an easy one.

I have one column (accountnum) with following rows:

123

213 and so on.

I need tu build a hierarchy so three levels where associated sth like this:

Level1 Level2 Level3

1 12 123

2 21 213

Any ideas?

How to build a hierarchy?

Fri, 14 Aug 2009 01:30:26 GMT

Hello,

are those the only accountnums you have for the establish heirarchies? If so you can do the following if statements during your load:


LOAD accountnum,
if (accountnum = 2 or accountnum=1, 'Level1', (if (accountnum=12 or accountnum=21, 'Level2', if(accountnum=123 or accountnum=213,'Level3','')))) as Levels ;
SQL SELECT *
FROM `C:\DOCUMENTS AND SETTINGS\YSUN\DESKTOP\test.xls`.`Sheet1$`;

It works when I tested it. You can adjust if statements accordingly if your criteria changes.

How to build a hierarchy?

johnw — Fri, 14 Aug 2009 03:12:02 GMT

I suspect your real situation is more complicated, but for the simplistic data you presented:

LOAD accountnum, 'Level' & len(trim(accountnum)) as level

How to build a hierarchy?

Fri, 14 Aug 2009 03:20:45 GMT

JW's solution is way better than mine:)

How to build a hierarchy?

johnw — Fri, 14 Aug 2009 03:54:14 GMT

Actually, we're probably both off track. This was probably intended to be an actual QlikView hierarchy using the hierarchy load, which I've never used. Probably something like this:

HIERARCHY (accountnum,parent,level)
LOAD
accountnum
,left(accountnum,len(trim(accountnum))-1) as parent
,accountnum as level

How to build a hierarchy?

Tue, 18 Aug 2009 15:37:07 GMT

Thank you for directing me on the right path. That is what I needed.

Regards