Issues storing data into a new table

Eduardo Espinoza

Forums

Hello Brian
 
I am working in a credit analysis and I need to create a database from scratch and populate it with data taken from another database, this process is repeated several times with different tables in a loop, but all of them have a similar structure where all the fields (except the last one) are numeric with two (or four if needed) decimal places and the last field is a date field.
My data has usually decimals values and sometimes low integer values, that is why it  is very important for the analysis to preserve the data exactly as it is.
I have been having a hard time trying to take the original values in numeric format (double or long) from the source table to write them into the new table because when I store the values in the destination table some of the values in the last row (do not know why) are written wrong, the problem I have, I have noticed it is when I write them in the new table.
Due to that, for the time being I captured the data as string, and created the new table just with character fields in order to write the data and later modify the field types.
How do you recommend me to do that in order to secure the data quality and to manipuate the data in its original format (numeric/date) in each case.
The IDEAScript is bigger than the code I attached, but the attached shows part of what I am trying to do with the data.
Thanks in advance!
 
Note: In the images of the tables it is shown the way I need to process the data (the row for the data in the second table is different of the result of the SampleScript it is ok in this case) and the script does not gives wrong data, with this IDEAScript, due to it is not in the loop when it stores the data.

Brian Element Thu, 11/22/2018 - 07:21

Hello Eduardo,

I am just trying to understand what the output should be.  In your two images above is the second image what you are looking for?  I ran your script (thanks for sharing) and obtained the following:

So I am just trying to understand what you want as your final product.

Thanks

Brian

Eduardo Espinoza Mon, 11/26/2018 - 11:02

Hello Brian
 
 
Thanks for so quick answer.
 
Sorry, I guess my description was messy and unclear.  I tried to simplify the example in the sample script I attached in the first publication since the whole IDEAScript is bigger and it contains other parts that are working fine,  I think that simplification was not clear enough. I am afraid my English skills are limitted and I did not explained it fine.
 
My problem is that when I write the data into the new table it makes errors in writing the data, for example instead of write 0,97 or whatever number expected, in the last row of the new table, after writting some fields the written data has errors or even 0,00 when other values are expected (wrong data).  When the data is written in a new table that contains just character fields the information is written fine (as in the example I sent the first time), but this prevent me to do several calculations before to write the data.
 
I am sending the whole part of the script that process those tables and you are going to be able to see the errors in the last row of some tables (see the files with the names starting with Brian), I think when you see them, you are going to understand what the problem is.
 
Inside of the script I put some explanatory comments for you
 
Thanks you very much!

Brian Element Mon, 11/26/2018 - 14:16

Hi Eduardo,

Ok, I think I found the problem and I have attached an updated script.  IDEA sometimes doesn't like when you do too many things in the same function.  In the second function you were reading the database and putting info in an array, then creating a new database, adding the info, adding a field and then a chart, for whatever reason IDEA didn't like it.  I broke up the second function into four additional functions and this seems to have fixed the problem.  Check it out and let me know if you are still finding some errors.

Thanks for the challenge, nice script.

Brian

Eduardo Espinoza Tue, 11/27/2018 - 11:30

Hello Brian
 
I ran the updated IDEAScript and checked the resulting data.  It worked perfect! (as expected).  You fixed the issue, thanks a lot for that and for your time.
 
By the way, I have to tell you that in the process of fixing the script you clarified to me the way to use arrays as parameters between functions, because that was something I had never done, so now with your explanation and the use of the arrays as parameters I am going to be able do the heavy work divided into separated functions to void this kind of issues in my scripts.
 
Thanks again!
 
Eduardo E.

Brian Element Tue, 11/27/2018 - 11:40

Glad that it is working, I enjoyed how you set-up your script.  You might have noticed that I place your two dimensional arrays as global variables and didn't pass them as parameters.  IDEAScript doesn't do arrays very well, especially multi-dimensional ones and the only way I could pass the information between functions is to set them up as global.  You will also notice that many of my scripts use global variables for the more common items, that is because I am lazy lol, good coding practice should have them as really being local and passing them back and forth but lots of times I skip this and just have them as global.

As you saw in your script you really need to break things down into functions, especially when you are working at the record level as it can have some strange effects sometimes on your results that having each item in a separate function seem to get rid of.

Good luck with your project.

Brian

Brian Element Mon, 11/26/2018 - 13:02

Hi Eduardo,

Thanks for the files and additional explanation.  I did find an alternative way of doing this that breaks the file apart and then puts it back together, not quite as efficient as you are doing but an alternative.  I just want to check, is the problems you are refering to the Error in the last line of the files you create or are there other errors, from some of your comments it seems the numbers are not matching properly, can you give me an example as I haven't see any yet, only the Error on hte last line.

Thanks

Brian

Eduardo Espinoza Mon, 11/26/2018 - 14:27

Hi Brian,
 
That is exactly the problem:   "the problems I am refering are the Errors in the last line of the files I create", in some cases there are errors and in others I get not matching data with the expected value (this last, is not going to be found in the version of the last script I sent you, just the errors).
So yes, the problem in this Script is the errors in the last line of the files, maybe there is something in the loops or in the variable types, I don't really know.
 
Thanks!
 
Eduardo E.

Brian Element Mon, 11/26/2018 - 14:56

Hi Eduardo, did you see my other post above.  I think I posted it the same time as you were posting this so you might have missed it.  I think I found the problem so check it out.

Brian

Eduardo Espinoza Mon, 11/26/2018 - 16:16

Hi Brian
 
 
 
You are right, I missed it, I am going to check the script and let you know my comments.
 
Thank you very much for all this help, no doubt, it is really helpful!
 
 
 
Eduardo E.