Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

What's the alternative of "Max" in load script?

Hi guys!

I have a question: What's the equivalent or alternative of "max" in load sript? I'm doing an aging report and I must base my starting point as the last payment or last due date. So I want to get the max(due_date) for each account so I can start the aging. But unfortunately, "max" is not allowed or invalid to use in the load script. Is there an alternative for "max" in the load script? Or is there any other work around for this problem?

Hope that you can help me solve my problem..

Thanks in advance!

Lester

1 Solution

Accepted Solutions
Not applicable
Author

Max works fine for me in the load script but bear in mind you have to include the "group by" line e.g.

Table1:

load a,

b,

max(c) as max_c

from mydata.xls group by a,b;

View solution in original post

4 Replies
tresesco
MVP
MVP

Hi,

First you have to group the records on 'account' . then again sort in ascending order on same. Now your are having the max(due_date) as the last record value for each account. Now use PEEK function to fetch the last record account wise. Your work is done.

Hope this would help you.

regards,

tresesco

Not applicable
Author

Max works fine for me in the load script but bear in mind you have to include the "group by" line e.g.

Table1:

load a,

b,

max(c) as max_c

from mydata.xls group by a,b;

Miguel_Angel_Baeyens

Hello,

I'd use something like

RangeMax(Peek(Date), Date) AS MaxDate
to create a new field with the maximum value. As it is, it doesn't make much sense, as you can easily use, once loaded, the
Max(Date)
to get the absolute maximum date. Anyway, you can use lookup() or previous() functions in addition.

Not applicable
Author

Hi guys!

Thank you all for your replies..

@Cowboy - yes! that what I'm missing.. Thanks!

Regards,

Lester