Creating Lots of Data in Script with For Loops, Need Help Optomizing

[justaqlikker]

INTRODUCTION

Greetings community, I need help finding the most efficient way to create a large amount of data.  It takes 20 hours to create 1 million records and I would like to make sure my code is as efficient as possible

SUMMARY

I have lots of financial data related to specific rental contracts.  The financial data are ONLY available as totals for the entire contract with start dates and maturity dates provided.  From this data I need to create monthly records which programmatically allocate the total across each month during which the contract is/was valid.

For example, if Contract #A123 having a total of $1200 payments has a start date of Jan 1, 2015 and an end date of Dec 31, 2015, then I would need to take this contract and turn it into records such as this:

A123  Jan 1, 2015  $100

A123  Feb 1, 2015  $100

A123  Mar 1, 2015  $100

...

A123  Dec 1, 2015  $100

I am of course, simplifying things.  Many contracts have different customer behaviors and different resulting financials.  Based on customer behaviors, different filters will be applied and afford different logic and monthly amount calculations.

DISCLAIMER

I know that this involves moderately heavy ETL code which QlikView is capable of accomplishing, but may not be optimized for processing large amounts of data.  Oracle PL/SQL would certainly handle this better; however, I no longer have the access to place Oracle code into production.

MY SOLUTION

The psuedo-code of solution is as follows:

1. Load all of the contract summary data into table "ContractSummary"
2. Create a new temporary table named "tempSpecialContracts", of ContractSummary records matching filter criteria
3. Get the count of all the records in tempSpecialContracts
4. Open FOR LOOP #1 with the count from step #3, use the Peak() function to get the contract number (primary key), dates, duration in months, and total amount for each record.  Calculate the monthly payment amount from total amount and number of months.
5. Within FOR LOOP #1, open FOR LOOP #2 with each contracts duration in months.
6. Loop through each one of the number of months, incrementing the date from the start date and then writing a record to a monthly transaction table with the Contract Number, Special Contract Type, Transaction Date, and Transaction Amount.
7. Drop the table tempSpecialContracts
   

THE CODE

Here is a snippet of the code I have written, see attachment.

THE ASK

Please take a look at my code and tell me if you think there is room for optimization.  For example, perhaps there is a more efficient way to write out the new records than an INLINE load.  I would appreciate any ideas that would make this run faster.

ABOUT ME

I am a long time reader, first time poster under this account.  My previous account seems to have been lost with an unrecoverable password.  I have been working with QlikView since 2012 and Oracle PL/SQL since 1999.

[rwunderlich · Accepted solution]

I think your approach would be slow  In my experience, iterating rows in a for loop is usually the slowest approach. Here's an alternative that may be simpler and much much faster. I've simplified, but you should be able to add in your filters and such.  In your description you said you wanted the monthly payment, but your  code example seems to show the accumulated payment. This code technique using IterNo() would work for either.

BaseData:

LOAD *, RecNo() as Key

INLINE [

Contract Start Date,  Term Months,  Contract Revenue

1/1/2015, 12, 1200

6/1/2015, 14, 1800

8/1/2000, 24, 3000

]

;

Monthly:

LOAD

  Key,

  MonthEnd(AddMonths([Contract Start Date],IterNo()-1)) as PaymentMonth,

  round([Contract Revenue] / [Term Months],1) as Payment

Resident BaseData

WHILE IterNo() <= [Term Months]

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[justaqlikker]

Wow Rob, this is wunderful! 😉

I didn't know it was possible to do such a thing.  I am translating my code to your method and I will reply back soon with my test results.  Thank you!!!  I was truly stuck and you have given me lots of hope.

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).

If not, please make clear what part of this topic you still need help with .

[justaqlikker]

I was able to easily convert my inefficient nest FOR loop to your method, Rob, and the results are unbelievably better.  A data set that takes my old code 10 minutes to process into 65,000 rows of monthly data is executed in 1 second using your design.  WOW!!!  I am so very thankful!  Everything validates out perfectly as well.  I decided to push your design even further and it produces 760,000 monthly records in 6 seconds!!!

Regarding your question of the "accumulated payment" in my example, this is used in a secondary monthly depreciation example and I forgot to remove it.  Thanks for pointing out that it could still be handled.  It is working beautifully.  Now my old code really just serves as psuedo-code for helping me with the conversion.  I will hold on to the old code as an example for the next time I might need to do something similar with lower volumes.

[rwunderlich]

Glad to hear you got such great results!

-Rob

http://masterssummit.com

http://qlikviewcookbook.com