Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
Nothing could be easier. See attachment.