Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
For P&L / BS needs, I'd like to group source data consisting of accounts, their parets and amounts into "nice-to-analyze" structure. The attached example shows the details.
I'll be grateful for any help.
PS I couldn't find a possibility to upload qvw example. Has it been disactivated?
BR,
Przemek
your table must be like this
test:
load
Account_id,
Account name,
Pernrt_id,
Amount
from tab1;
Now-----------------
load
Account_id
Accout name
resident test;
load
Pernrt_id as Account_id,
Amount
resident test;
drop table test;
Thanks for your answer, Anwar.
This script, however, allows to build only one level of the structure but I need to build 4 levels (groups). Do you know how to "extend" the script to 4 levels as in the attached example?
BR,
Przemek
Research semantic links in the scripting documentation. It should do exactly what you are looking for.
What I did for a P&L report was to build an excel template where I specify the hierarchies and then use Intervalmatch on the accounts. Should be fairly easy in this case
[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/3755.P_2600_L_5F00_Template.xls:550:0]
Then use
Intervalmatch (Account_Id) LOAD Account_id_start , Account_id_end RESIDENT P&L_Template;
Isn't this the exact situation in Book 1, 27.5 of the documentation? It suggests using Hierarchy and Hierarchybelongsto
Or does that not work?
I've checked tutorials and found nothing of any help. QV's "Help" says too generally for me to introduce the function. What scripting documentation did you mean and could you tell where to find it?
BR,
Przemek
jhpark's answer is exactly correct. Book I, Part IV, 27.5 (Page 487) is exactly where the answer is.
The link to the documentation is http://ftp.qliktech.com/QvWebDownloads/Documentation/QlikView%208.50%20Reference%20Manual%20ENG.pdf
So let's extend this by 1) Creating a group that links together the multiple levels of the hierarchy
2) putting values in at levels above the bottom level.
node parent value
A - 100
AA A 10
AB A 20
Right now, the value for A shows up as 130 (sum of A, AA, and AB), where I would rather have it show up as 100. ie, the value I assigned explicitly for A is the entire value I want for A -- I don't want AA and AB rolling up to A as well. How can that be accomplished?
(This happens whether I use a cyclic group or a drill down group.)
I have also used this structure, which seems to be very nice.
Unfortunately I am confronted with some design problems.
I have my P&L now splitted into the different departments (the columns).
When I use the "if(RowType=1, RGB(10,10,10))" for the background of the dimension, I ONLY GET cells with amounts COLOURED. So Cells without figures or filled with Zero ARE NOT COLOURED, which i would like to change 😕
Could anybody additionally tell me how to get the headers bold ? I have the Departments in the columns, so just setting "Text" to "bold" is not working 😞
I would be very grateful for any responses!
regards,
Christoph