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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)