topic Re: Sub Accounts in QlikView

Sub Accounts

vinicius_siquei — Thu, 05 Sep 2013 20:34:20 GMT

Folks - Please your help.

What's the best practice when I have to work with sub accounts.

For instance:

When the account '3.0.1' actually means the sum of all the accounts below whats your recommendation ?

I know that I could use left(account_num,5) = '3.0.1' - But there's any best method to do this ?

Regards,

Vinicius Siqueira

Re: Sub Accounts

whiteline — Fri, 06 Sep 2013 19:34:05 GMT

Hi.

It depends on what you want to achieve. The term 'to work with' is too broad to talk about the best practice.

If the sum and hierarchy for pivoting is the only intention you could create two fields left(account_num,1) and left(account_num,3) in addition considering them as account and sub account group.

There are differet tasks and different ways to handle hierarchy structures.

Re: Sub Accounts

vinicius_siquei — Thu, 12 Sep 2013 03:24:40 GMT

Thanks for your answer.

Yes, my intention is for instance, to sum from account 3.00.01 to 3.00.06.

What I would like to have is some intelligent way to go through these accounts.

Your suggestion to create levels of account is very interesting.

There's any other way?

Re: Sub Accounts

tresB — Thu, 12 Sep 2013 04:09:40 GMT

May be you can try something like :

Subfield(Account_num, '.' , 1) as ParentAcc

Subfield(Account_num, '.' , 2) as Child1

Subfield(Account_num, '.' , 3) as Child2

Re: Sub Accounts

Anonymous — Thu, 12 Sep 2013 04:13:26 GMT

I agree that group hierarchy is the way to go.

so you could name each section of your account code differently and split them in the script.

LOAD INLINE *[

Group , SubGroup,LowestGroup

3 ,00 ,01

3 .00 .06

];

and so on.

Re: Sub Accounts

whiteline — Thu, 12 Sep 2013 08:19:20 GMT

Hi.

Another example is a bit more complex. You create different dimentions for each level but also add records for each node (account and top subaccount) with empty values for childs. Suppose that each string has a unique id. It allows you to connect the hierarchy with facts that have different level of aggregation. For example planning facts and sales facts. And show them together in one pivot for comparison.

Re: Sub Accounts

vinicius_siquei — Thu, 12 Sep 2013 14:36:07 GMT

Very Good!