topic Accounts hierarchy in QlikView

Accounts hierarchy

Wed, 30 Jun 2010 19:42:22 GMT

Hello,

I have a table like this:

Account - Name - Indent

1 - Financiación - 0

10 - Capital - 1

100 - Capital Social - 2

1000001 - Capital Social - 4

1001000 - Capital Privilegiado - 4

101 - Fondo Social - 2

102 - Capital - 2

2 - Activos fijos - 0

28 - Amortizacion de activos fijos - 1

281 - Amortización inmov. inmaterial - 2

2810 - Amortizacion inmov. inmaterial - 3

2810000 - Amortizacion inmov. inmaterial - 4

And I have to show it with a pivot table with hierarchy like:

Account - Name - Amount

1 - Financiación - 1000

10 - Capital - 1000

100 - Capital Social - 1000

1000001 - Capital Social - 500

1001000 - Capital Privilegiado - 500

2 - Activos fijos

It's possible?

Thanks,

Oscar.

Accounts hierarchy

Wed, 30 Jun 2010 20:50:05 GMT

Something like this:


Temp:
load * inline [
Account - Name - Indent
1 - Financiación - 0
10 - Capital - 1
100 - Capital Social - 2
1000001 - Capital Social - 4
1001000 - Capital Privilegiado - 4
101 - Fondo Social - 2
102 - Capital - 2
2 - Activos fijos - 0
28 - Amortizacion de activos fijos - 1
281 - Amortización inmov. inmaterial - 2
2810 - Amortizacion inmov. inmaterial - 3
2810000 - Amortizacion inmov. inmaterial - 4] (delimiter is '-');

AccountChart:
load
 Account as Account0
 ,Name
resident Temp
where Indent = 0;

concatenate
load
 left(Account, 1) as Account0
 ,Account as Account1
 ,Name
resident Temp
where Indent = 1;

concatenate
load
 left(Account, 1) as Account0
 ,left(Account, 2) as Account1
 ,Account as Account2
 ,Name
resident Temp
where Indent = 2;

concatenate
load
 left(Account, 1) as Account0
 ,left(Account, 2) as Account1
 ,left(Account, 3) as Account2
 ,Account as Account3
 ,Name
resident Temp
where Indent = 3;

concatenate
load
 left(Account, 1) as Account0
 ,left(Account, 2) as Account1
 ,left(Account, 3) as Account2
 ,left(Account, 4) as Account3
 ,Account as Account4
 ,Name
resident Temp
where Indent = 4;


drop table Temp;

See the example in attachment.

Accounts hierarchy

Thu, 01 Jul 2010 07:53:56 GMT

Thank you Nick for your quickly answer, it is very interesting but what I really need is something like this:

I did it with sql but need to know if is possible do it in Qlikview.

Best regards.

Accounts hierarchy

Thu, 01 Jul 2010 13:15:38 GMT

Nothing could be easier. See attachment.