topic Re: Exclude Max value product using script in QlikView

Exclude Max value product using script

Shubham_Deshmukh — Sat, 16 Nov 2024 03:56:28 GMT

Hi Qlikers,

It is quite simple but m getting confuse for this in scripts. I want to exclude max value product (D) from my list to show in straight table, I am extracting below data from excel sheet.

PRODUCT	Value
A	10
C	20
D	30
E	40
E	50
D	90

Expected output :

PRODUCT	Value
A	10
C	20
E	40
E	50

Thanks

Re: Exclude Max value product using script

tresB — Fri, 05 Apr 2019 06:35:23 GMT

Max in aggregate or in individual value?

Re: Exclude Max value product using script

Shubham_Deshmukh — Fri, 05 Apr 2019 06:40:59 GMT

Individual value.

If possible let me know both.

Re: Exclude Max value product using script

tresB — Fri, 05 Apr 2019 07:00:41 GMT

This?

T1:
Load * Inline [
PRODUCT,	Value
A,	10
C,	20
D,	30
E,	40
E,	50
D,	90];

Load
	FirstSortedValue(PRODUCT, -Value) as MaxPROD
Resident T1 ;
T2:	
NoConcatenate
Load 
	* 
Resident T1 Where Not Exists(MaxPROD,PRODUCT);		
Drop Table T1;
Drop Field MaxPROD;

Re: Exclude Max value product using script

Shubham_Deshmukh — Fri, 05 Apr 2019 07:00:08 GMT

Thanks Tresesco for reply.
But tt is excluding Product 'E' also.

Re: Exclude Max value product using script

jyothish8807 — Fri, 05 Apr 2019 07:00:19 GMT

Hi Shubham,

You can acheive the same from UI a well, using the same logic mentioned by tressesco.

Ref attached.

Re: Exclude Max value product using script

Shubham_Deshmukh — Fri, 05 Apr 2019 07:01:33 GMT

Sorry Jyothish, I cannot open qvw here.

Re: Exclude Max value product using script

tresB — Fri, 05 Apr 2019 07:02:54 GMT

I got:

Re: Exclude Max value product using script

jyothish8807 — Fri, 05 Apr 2019 07:04:47 GMT

Try like this:

T1:
Load RowNo() as Si,
PRODUCT,
Value;
Load * Inline [
PRODUCT, Value
A, 10
C, 20
D, 30
E, 40
E, 50
D, 90];

In UI: Create a straight chart:

Dim1: Si

Dim2. =if(PRODUCT<>FirstSortedValue(total PRODUCT, -Value),PRODUCT)

expression:Sum(Value)

You can hide Si field from presentation tab:

Re: Exclude Max value product using script

Shubham_Deshmukh — Fri, 05 Apr 2019 07:25:38 GMT

Thanks @jyothish8807 , working absolutely fine but I want to do it in script.

@tresB , it is working for table box but when I am taking PRODUCT as dimension in straight table it is not showing 'E'.

Re: Exclude Max value product using script

tresB — Fri, 05 Apr 2019 07:32:58 GMT

Subham,

That is pretty obvious. You are doing a basic mistake. E has two values and when you use '=Value' expression it would not be able to two values, you should rather use Sum(Value) as expression.

You might want to Like and close the thread.

Re: Exclude Max value product using script

Shubham_Deshmukh — Fri, 05 Apr 2019 08:42:58 GMT

@tresB , I tried it before also still shows the same.

Re: Exclude Max value product using script

jyothish8807 — Fri, 05 Apr 2019 08:46:24 GMT

There seems to be an "if" condition on your dimension, can try removing it and check ? The solution from @tresB should work

Re: Exclude Max value product using script

Shubham_Deshmukh — Fri, 05 Apr 2019 08:48:10 GMT

@jyothish8807 , Ya my mistake, Sorry.

Thanks @tresB , your solution working fine.

THanks @jyothish8807 .