topic Re: Using hierarchy with qualify in QlikView

Using hierarchy with qualify

Thu, 18 Jul 2013 22:03:47 GMT

I am getting errors when trying to use hierarchy. The following is a simple example that illustrates what I am trying to do

If I have the following in my script, everything runs just fine.

MyTable:

Hierarchy(ID, ParentID, Name)

LOAD ID, ParentID, Name

INLINE

[ ID, ParentID, Name

1, 0, root1

2, 0, root2

3, 1, 1-1

4, 3, 1-1-1

5, 2, 2-1

6, 3, 1-1-2];

However, if I add the qualify statement at the top, reload errors out. (Error: HIERARCHY parameter NodeName (="Name") is not a valid field)

QUALIFY *;

MyTable:

Hierarchy(ID, ParentID, Name)

LOAD ID, ParentID, Name

INLINE

[ ID, ParentID, Name

1, 0, root1

2, 0, root2

3, 1, 1-1

4, 3, 1-1-1

5, 2, 2-1

6, 3, 1-1-2];

I tried adding table name as the prefix to the parameters, but that didn't help.

Any explanation would be appreciated.

Thanks,

Wei

Re: Using hierarchy with qualify

olac — Fri, 06 Sep 2013 18:09:27 GMT

I notice the same thing. Did you ever get around this?

Re: Using hierarchy with qualify

hic — Fri, 06 Sep 2013 19:54:38 GMT

Hierarchy is quite a complex transformation that uses several steps and creates temporary tables, so that it unfortunately is incompatible with the Qualify statement,

So I can only say: Don't use Hierarchy together with Qualify. Rename the fields some other way.

HIC

Re: Using hierarchy with qualify

kedar_dandekar — Fri, 06 Sep 2013 20:37:04 GMT

Hi Henric,

While trying to figure out a probable solution for this, I found that when I referred to fields in the Hierarchy() with a suffix of: <TableName>-NodeList, the Qualify seemed to work.

Is this an alternative or would it fail in most other scenarios?

e.g.

QUALIFY *;

MyTable:

Hierarchy([MyTable-NodeList.ID], [MyTable-NodeList.ParentID], [MyTable-NodeList.Name])

LOAD ID, ParentID, Name

INLINE

[ ID, ParentID, Name

1, 0, USA

2, 0, UK

3, 1, CA

4, 3, SF

5, 2, LN

6, 3, LA

7, 4, OAK

];

The resulting columns get qualified as <TableName>.<TableName>-NodeList.FieldName

any inputs on the same would be most appreciated.

Re: Using hierarchy with qualify

hic — Fri, 06 Sep 2013 21:38:53 GMT

I am not surprised that this works, but I am not sure that I want to support this solution...

The question to me is, why do you want to rename the fields? Or rather, why not do it properly? You can read what my view is on How to Rename Fields.

HIC

Re: Using hierarchy with qualify

olac — Wed, 11 Sep 2013 13:33:52 GMT

Ok thanks Henric. Not a big issue just nice to know the reason, helps to remember it.

Re: Using hierarchy with qualify

robert_connelly — Fri, 27 Dec 2013 22:21:34 GMT

Wei & Ola,

I know this is an older post, but I just came across this issue today and was able to create a work around. In my scenario I am loading data from a flat file. This file contains a meta data export from Essbase. Since I am loading a number of dimensions with the same file/field layout, I used a variable to prefix the field names during the hierarchy import. This got me around the qualifier issue. Below is an example, hopefully this helps.

Just keep in mind, the code below is only being used for demo/POC purposes. I am not sure that this would be the best scenario for a production app.

Thanks

Bob

Re: Using hierarchy with qualify

olac — Sat, 28 Dec 2013 00:41:54 GMT

Ah clever. Thanks a lot for remembering us and sharing