topic Re: chose values by max date according to 2 fields in QlikView

chose values by max date according to 2 fields

anatle1234 — Wed, 25 Nov 2020 16:16:04 GMT

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

Anil_Babu_Samineni — Mon, 15 Jan 2018 11:13:14 GMT

Script / UI ??

anatle1234 — Mon, 15 Jan 2018 11:14:10 GMT

script only

Anil_Babu_Samineni — Mon, 15 Jan 2018 11:26:44 GMT

Not tested, But can u try this?

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

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;

anatle1234 — Mon, 15 Jan 2018 11:34:17 GMT

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

Anil_Babu_Samineni — Mon, 15 Jan 2018 11:48:08 GMT

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;