Hierarchy Function not working properly in QV 11 SR4

[sathishkumar_go]

Hi, I have used "HIERARCHY" function in my application. It was used in QlikView 9. Same application i have refreshed in QlikView 11 SR4. refresh took more time and no of records increased. Herewith I have attached the QVW. In the attached QVW, I have refreshed in QV9 it gives 50 lines fetched. But same application I have refreshed in QV 11 SR4, It gives 383 lines. Is this bug in QlikView? Regards Sathish

[sathishkumar_go]

In QV 9:

In QV 11 SR4:

[hic]

Can you post some data?

HIC

[sathishkumar_go]

Hi Henric,

Thanks for your reply.

Please find the below mentioned script.

DATA:
LOAD * INLINE [
Level1, Level2, Qty
, 1,
1,2,
1, 10, 1
1, 100, 0.01
1, 101, 0.05
1, 102, 0.99
2, 3, 1
2, 20, 0.95
2, 200, 5
2, 201, 0.08
3, 4, 2
3, 30, 1
4, 400, 0.09
4, 401, 0.07
4, 402, 3
10, 1000, 0.39
10, 1001, 0.10
10, 1002, 30
20, 2000, 0.78
20, 2001, 5
20, 21, 0.25
21, 2100, 3
21, 2101, 3
30, 3000, 0.5
30, 3001, 0.5
];

tmpHierarchy:
LOAD
Level1 as PARENT,
Level2 as Child,
Level2 as HierarchyLevel,
Qty
RESIDENT DATA;
DROP TABLE DATA;

ItemHierarchy:
Hierarchy(Child, PARENT, HierarchyLevel, ParentName, HierarchyLevel, Path, '/')
LOAD
Child,
PARENT,
HierarchyLevel,
Qty
RESIDENT tmpHierarchy;

DROP TABLE tmpHierarchy;

Regards

Sathish

[hic]

It does look like a bug - both in QV9 and in QV11. It should create 25 lines, not 50 (QV9) and certainly not 382 (QV11). I'll report it to our testers.

It works fine if you avoid the resident loads, e.g. by

ItemHierarchy:

Hierarchy(Child, PARENT, HierarchyLevel, ParentName, HierarchyLevel, Path, '/')

LOAD

  Level1 as PARENT,

  Level2 as Child,

  Level2 as HierarchyLevel,

  Qty

INLINE [

Level1, Level2, Qty

...

HIC

[sathishkumar_go]

Hi Henric,

Thanks for your response.

Please find the attached QVW.

In the attached QVW, I am able to refresh in QV9 and "HIERARCHY" is working properly.

IN QV9, 490,279 LINES FETCHED. But hierarchy works correctly.

But same QVW, I am not able to refresh in QV 11 SR4.

In QV11 SR4:

it still refreshing...

I believe its an QlikView bug. because same application with same script, QV9 is working fine but In QV 11, gives more records and keep on refreshing.

Kindly help me to resolve the issue.

Regards

Sathish

[hic]

No this is not a bug. It is the wanted behaviour. What happens here, is that you load your hierarchy from a table called "temp", that has duplicate rows of the same node. It shouldn't. It should have one record per node. If a node has a duplicate, then all sub-nodes will be duplicated also. And you have plenty of nodes with more than 100 duplicates...

Why don't you just create your path (for the tree-view list box) manually in the Master Calendar? There is really no need for a Hierarchy statement:

     Year & '^' & HalfYear & '^' & Quarter & '^' & Month & '^' & Day as Calendar_Level

HIC

[sathishkumar_go]

Hi Henric, For me duplicate records is fine. But my question is  1) Why duplicate lines will be more in QV11 SR4 compare to QV9? 2) If you see the above log file, In QV9 gives 490279 lines (I know some duplicate lines are fetched). But same application refreshed in QV11 SR4, It gives more than 40852500 lines? 3) Why Same script gives two different outputs (no.of records) in different qlikview version? Why I am saying, This is BUG. Because QV9 gives "490279 lines" (With duplicate records) But in QlikView 11 SR4 gives more than 40852500 lines (refresh not yet finished). Due to business logic, we can't avoid the resident loads. (Whatever I have attached is the dummy data). Recently my client have migrated to QV11 SR4 in Production environment. same application will works in QV9 (DEV Environment) but its not worked in Production environment (QV11 SR4). And also, I don't have rights to change the coding. which someone has developed and approved by client. Now, My client is asking 1) Hierarcht function works differently based on the QlikView versions? 2) If not means, why QV11 SR4 gives more records when compare to qv9? 3) In QV11 SR4, Refresh not yet completed. it still going on "Hierarchy" only ? I hope you can understand my question. Please clarify the above mentioned question. Sothat I will inform to the client. Regards Sathish

[hic]

You're right that QlikView 9 works differently than QlikView 11, but as I see it QlikView 9 was too restrictive - it removed records if there were two records with the same node ID and the same parent ID. We have changed this, so that the developer is in control instead: If he wants to remove duplicates, he can do so with e.g. distinct.

Run the following script in the two versions, and you'll see that they produce different numbers of records, and that the third record disappears in QV9.

Hierarchy (NodeID, ParentID, NodeName)

Load *,

  RecNo() as RecNo,

  NodeID as NodeName

  inline

[NodeID, ParentID

Root,

Node1, Root

Node1, Root];

The problem in your model is that your source is not an adjacent nodes table. For instance, '2011-H1' should only have one parent - '2011'. But in your source this node occurs 1086 times. Further, '2011-Q1' should also have only one parent - '2011-H1', but instead it has 540. The effect is multiplicative, so this quarter (and every sub-node) will be generated 1086x540 times, i.e. half a million times. So I think you make a mistake when you say that "duplicate records is fine".

Finally, the purpose of the Hierarchy prefix is to generate an expanded nodes table. But your initial master calendar is already a nice, proper, fix-level hierarchy in an expanded nodes format. So, you don't need the hierarchy prefix. You already have the format you should have. All you need is to add a field "Calendar_Hierarchy" that you can use in a tree-view list box.

See more on http://community.qlik.com/docs/DOC-5334

HIC

[sathishkumar_go]

Hi Henric, Thanks for your constant support and reply!!!! I am not aware, "HIERARCHY" function works differently in QV9 & QV11 SR4. Do we have any release notes for QLIKVIEW 11 SR4 which they have mentioned "HIERARCHY" function works differently compare to the previous version? Could you please share the release notes. Sothat I will forward the client for the same. I do agree with you, For this example I no need the hierarchy function. Its just create the combination of the fields and front view use the seperator "^". But I have no rights to change the existing coding without the client and proper approvals. If we want to change the existing scripting, We have to submit the proper proof to the client. That is what i am asking the QlikView release notes. Regards Sathish