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

Order by & Only

Hi All,

If I have say a date column and client no and other columns (client number duplicated). To extract the last transaction made by a client, can I say order by then use "Only" for each line that I am uploading?

Thanks

H

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

Yes you can.

Load ClientNo,FirstSortedValue(Date,-Date),FirstSortedValue(Amt,-Date),FirstSortedValue(Qty,-Date) Group by ClientNo;

Load ClientNo,Date#(Date,'M/D/YYYY') AS Date,Amt,Qty Inline [

ClientNo,Date,Amt,Qty

1,1/1/2014,10,12

1,1/20/2014,12,15

2,1/1/2014,10,12

2,1/20/2014,12,15 ];

View solution in original post

7 Replies
Not applicable
Author

You should use group by

LOAD

client_no,

date(max(date)) as DateColumn

resident TableName

group by client_no;

Thanks,

Singh

PrashantSangle

Hi,

You can use FirstSortedValue() function in script which is aggregated function

then Use Group By in Where Clause.

You have to write all fields in Group by clause which you are not using in aggregated function.

Or you can use Only() to avoid that field to write in Group By clause

Your expression like this

FirstSortedValue(ClientNo,Date)

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

Thanks for this,

The group by clause; I am guessing assumes that for each client all the other columns that are being grouped by will have identical values?

My data is such that I have a client no, date column, and other fields which for each client may not be consistent.

For example lets say I have 4 entries for one client. Client no column will be the same for these 4, date column will be the same hence the max function which is fine, columns c to h may all be different for the 4 different transactions. When I do a group by using all the columns not included in the aggregation, I am still getting 4 columns back and I am assuming this is because of the differences in columns c to h?

How do I come up with one row for each client, purely based on the client no and picking up the max date, and what line corresponds with that max date?

Thanks

anbu1984
Master III
Master III

Can you post sample input and expected output?

Not applicable
Author

Ok, let me prepare something.

To avoid grouping by all fields, can I apply the first sorted value function to all the fields, based on the max date? (12 of them)

H

anbu1984
Master III
Master III

Yes you can.

Load ClientNo,FirstSortedValue(Date,-Date),FirstSortedValue(Amt,-Date),FirstSortedValue(Qty,-Date) Group by ClientNo;

Load ClientNo,Date#(Date,'M/D/YYYY') AS Date,Amt,Qty Inline [

ClientNo,Date,Amt,Qty

1,1/1/2014,10,12

1,1/20/2014,12,15

2,1/1/2014,10,12

2,1/20/2014,12,15 ];

Not applicable
Author

Perfect Anbu,

thank you very very much, works like a dream!!!