How to build a hierarchy?

[#1]

Hi,

It is propably an easy one.

I have one column (accountnum) with following rows:

1

12

123

2

21

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?

[#2]

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.

[johnw]

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

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

[#4]

JW's solution is way better than mine:)

[johnw · Accepted solution]

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

[#6]

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

Regards