How can I find a value except for the selected row

[maya_yurika]

Hi,

Can you help me with a code for the following example? I am trying to get a third column named 'min of other companies' which brings the minim value found for other companies except the one on the row.

Company	Price	min of other companies
AAA	80	"min(90,100)' = 90
BBB	90	"min(80,100)' = 80
CCC	100	"min(80,90)' = 80

Thank you

[QFabian · Accepted solution]

Hi @maya_yurika ,i made this script, considering that its always better try solving this kind of challenge in script.

It is going to support if there are more than one Price per Company, and more than 3 companies as you put in the example.

So you can just create the chart with the resulting fields :

DataSource:
LOAD * INLINE [
Company, Price
AAA, 80
BBB, 90
CCC, 100
];
// DDD, 5
// EEE, 666
// FFF, 37
// GGG, 1001

Company:
Load
rowno() as Id_Company,
Company as Company,
Price
Resident DataSource;
drop table DataSource;

For vI = 0 to NoOfRows('Company') - 1
Let vIdCompany = peek('Id_Company', $(vI), 'Company');
Company_Considered:
Load
$(vIdCompany) as Id_Company,
Id_Company as Id_Company_Considered,
Price
Resident Company
Where
Id_Company <> $(vIdCompany);
Next

left join (Company)
Load
Id_Company,
min(Price) as Min_Price
Resident Company_Considered
group By
Id_Company;

drop table Company_Considered;

[Saravanan_Desingh]

Try this in front end.

Min({<Company=e(Company)>}TOTAL Price)

[Saravanan_Desingh]

Output:

[Saravanan_Desingh]

One more thought. Add Avg({1}1) see all irrespective of the selection.

Avg({1}1)*Min({<Company=e(Company)>}TOTAL Price)

[Saravanan_Desingh]

Ouput:

[joaopaulo_delco]

I really would like to know how to do it in front-end 🤔

[Frank_Hartmann]

A little bit tricky and maybe you can further simplify the expression,  but possible and dynamic.

Load * Inline [
Company,Price	
AAA,80	
BBB,90	
CCC,100
];
//DDD,500
//EEE,50

Dim:

=Company

Exp:

=replace(left(Replace(text(Replace(Replace(Replace(Replace(Replace(concat(subfield(concat(Total Price,' ',Price),',',
	ValueLoop(1,100000))),Price,' '),',',''),'   ',' '),' ',','),',,','')),',',', '),3),',','')

[joaopaulo_delco]

Really insane!! 👏🙌

Thanks for share!