Add expression in a table

[#1]

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

[#2]

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

];

[giakoum]

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

[#4]

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

[giakoum]

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.

[#6]

That's really nice, thank you for your file Ioannis !