Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I have a simple table with one dimension (Company) and several expressions (turnover, costs_1, costs_2….)
My table is like this :
Company Turnover Costs_1 Costs_2 Costs_3 …
Toto 3000 300 200 100
Titi 2000 200 250 150
Tutu 1000 80 260 180
Tata 800 100 130 150
Tete 700 120 100 100
Toutou 500 100 80 60
… … … … … …
This table contains 30 companies and I would like to add lines “top 3” and “top 5” under the company which has rank 3 and under the company which has rank 5 (the criteria for rank is turnover).
I know the expression I want but if I add it as “expression”, I have a new column in my table and it’s not that I want. I need that “top 3” and “top 5” appear as lines, not as column, like this :
Company Turnover Costs_1 Costs_2 Costs_3 …
Toto 3000 300 200 100
Titi 2000 200 250 150
Tutu 1000 80 260 180
Top 3 6000 580 710 430
Tata 800 100 130 150
Tete 700 120 100 100
Top 5 7500 800 940 680
Toutou 500 100 80 60
… … … … … …
Do you know how can I have this result ?
Thank you
Regards
Bérengère
hi
in script
Originaltable:
load
Company ,
Turnover ,
Costs_1 ,
Costs_2 ,
Costs_3
from original table;
concatenate
Addedtable:
load * inline [
Company , Turnover , Costs_1 , Costs_2 , Costs_3
6000, 580, 710, 430
7500, 800, 940, 680
];
so what you need is to concatenate in your original table a new line as follows (it will autoconcatenate if you use the same exact fields) :
concatenate (table1)
load
'Top3' as company,
...
...
sum(Cost3) as Top3
where
rank(turnover) >= 3
group by
company
Hello !
Thank you for your answers. Actually I wanted to know if it was possible to have this result with options in GUI but I understand that the only way is to work with the script.
Thank you for your help !
Bérengère
Please find attached an other GUI solution, which however I do not recommend at all as it involves dynamic updates, which can prove really dangerous to the server environment and macros.
That's really nice, thank you for your file Ioannis !