Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

deere2013
New Contributor III

Create expression to return the largest number

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.

Tags (1)
1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Create expression to return the largest number

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.

13 Replies
Not applicable

Re: Create expression to return the largest number

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

deere2013
New Contributor III

Re: Create expression to return the largest number

ECMMaterialLead time
479894RXAEROETN193160
479894RXAEROETN933290
479894RXAERORE3373998
479894RXAERORE5563998
479894RXAERORE5637748
479894RXAERORE5720104
479894RXAERORE5720114
479894RXAERORE5720124
Not applicable

Re: Re: Create expression to return the largest number

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

AJ

mov
Esteemed Contributor III

Re: Create expression to return the largest number

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")

deere2013
New Contributor III

Re: Re: Create expression to return the largest number

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

Not applicable

Re: Create expression to return the largest number

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;

deere2013
New Contributor III

Re: Re: Create expression to return the largest number

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

mov
Esteemed Contributor III

Re: Create expression to return the largest number

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)?

deere2013
New Contributor III

Re: Re: Create expression to return the largest number

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

ECMMaxLeadtime
479894RXAERO8