Last value using AGGR or in the script

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

I am stuck with an issue and hope someone can assist.

I have a data set with transactions for Accounts. There are many rows per account and they are timestamped. There is also a FLAG to define a active record.

What I need to do is sum the BALANCE for last record, based of the timestamp, for each account where the flag is Y.

Currently I am using "AGGR( Sum({<FLAG={'Y'},[DATE]={'$(=Max(DATE))'}>}BALANCE) ,ACCOUNT)" but that does not work.

I am also wondering if it is possible or 'better' to somehow create a flag in the script that could be used? There is a potential of the data volumes growing and I am afraid of performance in the future if AGGR is used in all the objects.

The INLINE table looks as such - but I also attach an example QV app:

LOAD * INLINE [

ACCOUNT, FLAG, REGION, BALANCE, DATE

85263101984, N, AA, 83421.79, 2017-10-31 18:27

85263101984, Y, AA, 83421.79, 2017-10-31 18:27

85263101984, N, AA, 83421.79, 2017-12-05 09:42

85263101984, Y, AA, 83421.79, 2017-12-05 09:42

85263101984, N, AA, 83421.79, 2017-12-05 09:50

85263101984, Y, AA, 83421.79, 2017-12-05 09:50

85263578945, Y, BB, 522467.02, 2017-12-12 12:35

85263578945, N, BB, 522467.02, 2017-12-12 12:35

85263578945, Y, BB, 522467.02, 2017-12-15 11:18

85263578945, N, BB, 522467.02, 2017-12-15 11:18

];

Thank you for your assistance, so late in the week.

Re: Last value using AGGR or in the script

m_woolf — Fri, 29 Jun 2018 13:52:41 GMT

Try the attached qvw:

Re: Last value using AGGR or in the script

sunny_talwar — Fri, 29 Jun 2018 14:11:32 GMT

Have you tried this using FirstSortedValue?

FirstSortedValue({<FLAG = {'Y'}>}BALANCE, -DATE)

Re: Last value using AGGR or in the script

johngouws — Sat, 30 Jun 2018 05:58:37 GMT

Both these options work and return the correct answer.

I see I cannot mark both answers correct, but I do like the solution from m\w because it takes place in the script.

Thank you also Sunny.

Re: Last value using AGGR or in the script

johngouws — Sat, 30 Jun 2018 05:59:18 GMT

Thanks a lot. This is exactly what I was looking for.

topic Re: Last value using AGGR or in the script in QlikView

Last value using AGGR or in the script

Re: Last value using AGGR or in the script

Re: Last value using AGGR or in the script

Re: Last value using AGGR or in the script

Re: Last value using AGGR or in the script