Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
anatle1234
Contributor III
Contributor III

chose values by max date according to 2 fields

hi ,

I have this example:

I am not able to create max approval column. for each agreement I need to put there approval with lastest date.

I need to do it in the script for my main table, to add this column for each agreement.

can you please help.

thanks,

Anat

5 Replies
Anil_Babu_Samineni

Script / UI ??

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
anatle1234
Contributor III
Contributor III
Author

script only

Anil_Babu_Samineni

Not tested, But can u try this?

If(agreement = Previous(agreement), approval,'') as [max approval]

OR

Table:

Load * from table;

Final:

NoConcatenate

LOAD approval, agreement, date, If(agreement = Previous(agreement), approval,'') as [max approval] Resident Table Order By date desc;

DROP Table Table;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
anatle1234
Contributor III
Contributor III
Author

its still returns both projects for each agreement even though it should return only 1 for each agreement

Anil_Babu_Samineni

Oh, Is it? Then this should work

Table:

Load * from table;

Right Join (Table)

LOAD agreement, Max(date) as date Resident Table Group By agreement;

Left Join (Table)

Load agreement,FirstSortedValue(approval, date) as [max approval] Resident Table Group By agreement;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful