topic Re: How can I find a value except for the selected row in QlikView

How can I find a value except for the selected row

maya_yurika — Thu, 11 Mar 2021 20:06:29 GMT

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

Re: How can I find a value except for the selected row

QFabian — Fri, 12 Mar 2021 00:03:16 GMT

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;

Re: How can I find a value except for the selected row

Saravanan_Desingh — Fri, 12 Mar 2021 00:36:51 GMT

Try this in front end.

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

Re: How can I find a value except for the selected row

Saravanan_Desingh — Fri, 12 Mar 2021 00:37:19 GMT

Output:

Re: How can I find a value except for the selected row

Saravanan_Desingh — Fri, 12 Mar 2021 00:45:23 GMT

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

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

Re: How can I find a value except for the selected row

Saravanan_Desingh — Fri, 12 Mar 2021 00:45:55 GMT

Ouput:

Re: How can I find a value except for the selected row

joaopaulo_delco — Fri, 12 Mar 2021 01:31:07 GMT

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

Re: How can I find a value except for the selected row

Frank_Hartmann — Sat, 13 Mar 2021 13:20:49 GMT

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),',','')

Re: How can I find a value except for the selected row

joaopaulo_delco — Mon, 15 Mar 2021 00:47:12 GMT

Really insane!! 👏🙌

Thanks for share!