Help for Customer Hierarchy count

[#1]

Hello,

There is what i would like to do:

- I have a table like this

Article	Customer	Quantity
1	9999	20
2	9998	9

and another table

Customer	son customer
9999	10000
9998	9997
10000	10001
9999	10002
9997	9996
9997	9995
9998	9994

- I want to count the number of sons customers and divide the quantity with it.

There is what im looking for in this example:

Article	Customer	Quantity
1	10001	20/2 = 10
1	10002	20/2 = 10
2	9996	9/3 = 3
2	9995	9/3 = 3
2	9994	9/3 = 3

It should always take the lasts customers of the hierarchy

How could i do this? Maybe with the Hierarchy function or mapping?

Thanks for your help,

Regards,

Loïc

[#2]

Oh ok you're right!

I am trying to modify the app to make it work on my machine.

I just modified the "chr(39)" by " ' " (without spaces, only the quote char) and i don't have the same log file.

This time, the quote char does not repeat at all.

The sure thing is that the treatment of the quote char on my machine is not the same than yours....but why....

I really need to make your app working on my machine, it seems absolutely perfect for my problem that I've been working for long long hours now...

[evan_kurowski · Accepted solution]

Hello Loic_dev,

I'm glad we worked on this because I've had this nagging feeling there is something about the hierarchy function that is off, but just couldn't put my finger on it.  I've had inconsistent results in the past where that whole process of trying to loop through the values and chain them together and detect the depth, it wasn't necessary.  Everything is clear now and all that needs to be done is engage the later parameters of this function, however...

If you don't use the EXACT CORRECT arguments for hierarchy, the true capabilities of the function remain concealed.

To exacerbate the problem, the documentation of the hierarchy function in the manual has 3 issues:

• It is incomplete
• It contains inaccuracies
• It cites no functional code examples that illustrate its fully intended form

All that looping of code that was happening in the prior version of the apps in this thread, where its trying to create a chain of concatenation of the hierarchy tiers and add a delimiter?  That is basically trying to emulate what the hierarchy function would be doing if the later parameter arguments were applied, but with a lot more code, complexity, and convolution.  Toss it out!

Attached is a newer and more brief version, that does the same thing as before, but the entire chain assembly happens in the hierarchy function.  However I'm going to follow with a post that illustrates the disconnect from documentation and functionality, god only knows how many would-be adopters of QlikView this divergence of "reality" vs. "perception" has scared off or discouraged.

Revised copy attached:

[evan_kurowski]

My experiences are showing during testing that, if the script is attempting to use hierarchy() parameters #6 [PathName] and #7 [PathDelimiter] and #8[Depth], that parameter #5 is not an optional argument.

If you validly populate Arguments#1-#3 and any of #6-#8 without populating argument #5, the script will proceed through without error but parameters #6-#8 will be ignored

Argument #5 [PathSource] - Is a required argument if you intend to produce a node path from the results of the hierarchy() function

An example of a  value created for a node path would be a delimited chain like:

'Server\MyDocuments\MyPhotos'

'Division1~UnitAlpha~Squad3'

Argument #5 references the field name from the source table that contains the values that will be used to build the node path.

If Argument #5 is omitted, no node path will be created and arguments passed in #6 through #8 will have no impact on script generation (i.e. you cannot leave argument #5 blank, supply a literal field name for argument #6 and expect a node path constructed off of argument #3 using the field name supplied in argument #6)

If argument #5 is populated, and argument #6 is blank, the node path created will appear in the results table as a new field name called [Path].

If argument #5 is populated, and argument #6 is populated, the node path field generated in the results table will not use the default name [Path], but instead use the field name specified in argument #6

If argument #5 is populated, you may then use argument #7 to assign a path node delimiter of your choosing

[#5]

Hello Evan,

Thank you very much man, this is perfect!

Very helpful work on the Hierarchy function!

Great job, you rock!

Best Regards,

Loïc