How do I get the most current entry of a record associated to an account?

[#1]

Situation: I have two tables, one of the table include annual results of a commitments. And the other table includes the annual statement for each account associated to the commitment.

Now the worst scenario: Additionally to each annual statement per account, there might be changes in annual results on these accounts that pose problems to me.
For the analysis, I must, whether on account or commitment level, evaluate the data of the most recent annual result.
Sometimes there are more than five changes. Each annual result has a unique object ID, a result date and creation date. The account data contains only the Object ID (relating to the particular result statement) and the decision date.

My first try was using FirstSortedValue but I got not the recent values back.

How can I deal with this type of data?

Roswitha

[Gysbert_Wassenaar]

I don't understand how the changes are stored. You say you only have an object ID and a date. Where's the amount of the change then?

Please post a small qlikview document that demonstrates the problem.

[Oleg_Troyansky]

I agree with Gysbert that a small example could help us understand your problem... Having said that, I suspect that your calculations need to happen in the script, rather than in the front end. It would be extremely hard to calculate the accurate latest balance with several changes, on the fly.

I'd use a technique of calculating rolling balances - using the functions Peek() and Previous(). I'm sure there are several free white papers describing this technique. I'm also describing it in detail (hi, Gysbert!) in my book QlikView Your Business.

cheers,

Oleg Troyansky

[#4]

Hi Gysbert,

thanks for your reply.

I will add two excel sheets representing parts of the files, there are

more information in the tabel but for the calculation they don't matter.

The HGBresolution table is the main one and the HGBaccount the one with

details. The relationship between the tabels HGBresolution and HGBaccount

is 1 :n

The calculation should take place in the script, so that the analysis only

has the data which is needed.

Thanks for help.

Roswitha

Von: Gysbert Wassenaar <qcwebmaster@qlikview.com>

An: Roswitha Peirow <rpeirow@abit.de>

Datum: 03.02.2016 18:42

Betreff: Re: - How do I get the most current

entry of a record associated to an account?

How do I get the most current entry of a record associated to an account?

reply from Gysbert Wassenaar in New to QlikView - View the full discussion

I don't understand how the changes are stored. You say you only have and

object ID and a date. Where's the amount of the change then?

Reply to this message by replying to this email, or go to the message on

Qlik Community

Start a new discussion in New to QlikView by email or at Qlik Community

Following How do I get the most current entry of a record associated to an

account? in these streams: Inbox

© 1993-2016 QlikTech International AB | Copyright & Trademarks | Privacy |

Terms of Use | Software EULA

Falls Sie diese E-Mail ausdrucken wollen, pruefen Sie bitte der Umwelt zuliebe, ob der Ausdruck erforderlich ist.