topic Re: Create expression to return the largest number in QlikView

Create expression to return the largest number

Anonymous — Wed, 14 May 2014 15:51:42 GMT

Good morning/evening. I am trying to determine from the attached file where ECM has multiple Material and those respective material have different lead times. I would like to develop script for an expression to determine based on lead time for that ECM what is the highest lead time. Since there are duplicates of the highest number for now it doesn't matter.

Re: Create expression to return the largest number

Wed, 14 May 2014 15:58:51 GMT

I dont see an attached file. Can you please re-attach it.

Re: Create expression to return the largest number

Anonymous — Wed, 14 May 2014 16:19:55 GMT

ECM	Material	Lead time
479894RXAERO	ETN19316	0
479894RXAERO	ETN93329	0
479894RXAERO	RE337399	8
479894RXAERO	RE556399	8
479894RXAERO	RE563774	8
479894RXAERO	RE572010	4
479894RXAERO	RE572011	4
479894RXAERO	RE572012	4

Re: Re: Create expression to return the largest number

Wed, 14 May 2014 16:32:05 GMT

Maybe like attached. I created a field called RequiredFlag on the backend.

1 corresponds to all the materials for a particular ECM with max Lead Time

Hope it helps.

Thanks

Re: Create expression to return the largest number

Anonymous — Wed, 14 May 2014 16:34:37 GMT

If in the script:
load
ECM,
max("Lead time") as MaxLeadTime
RESIDENT...
GROUP BY ECM;

If on the front end:
ECM is dimension, and the expression is max("Lead time")

Re: Re: Create expression to return the largest number

Anonymous — Wed, 14 May 2014 16:34:44 GMT

I am only using the personal addition so need the script not a QVW.

Re: Create expression to return the largest number

Wed, 14 May 2014 16:50:44 GMT

Table:

LOAD ECM,

Material,

[Lead time]

FROM

Data Source;

Left Join

Load ECM,Max([Lead time]) as Flag

Resident Table

Group by ECM;

Test:

Load *, If([Lead time]=Flag,1,0) as RequiredResult

Resident Table;

Drop Table Table;

Re: Re: Create expression to return the largest number

Anonymous — Wed, 14 May 2014 17:10:33 GMT

getting an error for the invalid expression. I am adding another line in the script. I have also renamed the field Lead time to Prod Proc LT. the expression needs to have a rank? so I want 1.

max([PROD PROC LT],1) as Maxprodlt

Re: Create expression to return the largest number

Anonymous — Wed, 14 May 2014 17:14:23 GMT

Rank is optional, most likely you don't need it.

As for the error - can you show this portion of the script? Or upload the app (not too large please)?

Re: Re: Create expression to return the largest number

Anonymous — Wed, 14 May 2014 17:41:32 GMT

LOAD

Left(ECM,6) as [AE PMR ECM LCL],

ECM as [AE PMR ECM],

MATERIAL,

[PROD PROC LT] as [AE PMR PROD PROC LT],

Max([PROD PROC LT],1) as Maxprodlt,

Sorry I am not very versed in scripting development. So just to be clear I am wanting a variable that I can use. Typically I use list or table boxes so my intentions were that I have multiple ECM's and I was going to create a table that has the ECM and this new variable with the max Prod Proc LT. so it would look like this

ECM	MaxLeadtime
479894RXAERO	8

Re: Create expression to return the largest number

Anonymous — Wed, 14 May 2014 17:49:57 GMT

This will work:

Table:
LOAD
Left(ECM,6) as [AE PMR ECM LCL],
ECM as [AE PMR ECM],
MATERIAL,
[PROD PROC LT] as [AE PMR PROD PROC LT]
RESIDENT (or FROM) <your data source>;

LEFT JOIN (Table) LOAD
[AE PMR ECM],
Max([AE PMR PROD PROC LT]) as Maxprodlt
RESIDENT Table
GROUP BY [AE PMR ECM];

Basically it is the same as Ajay Prabhakaran suggests, just in a little different form.

Re: Create expression to return the largest number

Anonymous — Wed, 14 May 2014 18:09:44 GMT

I pasted the script as follows...now it tells me the table doesn't exist

[\\frxfileserv1\ftpdown\WW_PUBLIC\Download Folder\New Product Programs-PMR 2.0\Local\RX01\AERO_PMR.XLS]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LEFT JOIN (Table) LOAD

[AE PMR ECM],

Max([AE PMR PROD PROC LT]) as Maxprodlt

RESIDENT Table

GROUP BY [AE PMR ECM];

Re: Create expression to return the largest number

Anonymous — Wed, 14 May 2014 18:13:36 GMT

Sure, I don't know what is the correct name of your table

In my example it is Table. In your case - whatever you named it.

Re: Create expression to return the largest number

Anonymous — Wed, 14 May 2014 18:31:59 GMT

In my haste I deleted the script for the naming of the variable, but once I added that back in it works exactly as I was hoping it would. Thank you for the help. Obviously previous to this script it was a manual process to find the largest one. Now its one click!