topic Re: Account Schedule Rundown. in QlikView

Account Schedule Rundown.

johngouws — Tue, 14 Mar 2017 12:08:08 GMT

Good afternoon.

I am required to produce what is termed a Schedule Rundown. This may be called something else in another business. Anyway....

The attached spreadsheet and QlikView model has 4 accounts with related information. The logic is quite difficult to explain so I have some notes in the spreadsheet. In essence I am supplied the yellow column heading and need to produce the green column heading. In the spreadsheet there is a 'Paid' column which is only used in this case to show the excel solution. I do not use it in the model. To get the best feel for this expression either F2 on H5 or H27 to see the logic. I am hoping that the notes in the excel sheet will be sufficient.

The field I need to create is the 'Capital Paid' field. If you look at the expression in this field you will see it is quite a beast. Whether it is the most efficient expression is probably debatable, but the numbers it returns are correct.

What I need to do is produce the 'Capital Paid' field in the script so that I do not need to create it in every object.

If there is something not quite clear enough, please reply and I will see if I can explain a bit more.

Thank you very much.

Re: Account Schedule Rundown.

aarkay29 — Tue, 14 Mar 2017 15:03:38 GMT

PFA

Thanks,

Aar

Re: Account Schedule Rundown.

johngouws — Wed, 15 Mar 2017 06:05:30 GMT

Good morning Aar.

I really appreciate your reply and solution. It is SO close. I added a Diff column showing the difference between the Capital Paid and sum(CAPITAL) columns. When selecting the bottom 2 accounts you will see a difference. If you select the top 2 accounts there is no difference.

The difference between the 2 accounts is that the bottom 2 did NOT have installments due in the first period and the top 2 accounts did.

I tried figuring out your code in the script and making a few 'changes', in another version of course, but could not get it to work.

I attach the undated version with the Diff column and a snippet showing the differences between the 2 accounts.

I appreciate your help.

Re: Account Schedule Rundown.

aarkay29 — Wed, 15 Mar 2017 14:38:46 GMT

Just made a small change in the script for null handling.

Re: Account Schedule Rundown.

johngouws — Wed, 15 Mar 2017 14:57:10 GMT

Hello Aar,

That works 100% perfectly.

Thank you very much for the solution.

Regards - JG