Hi Brian,
I am new to scripting but hoping you can help assist. I have 100+ databases each named "Fund XXX". Each fund contains a column heading "trans" signifying a specific transaction code. I need to select 4 random samples with the "trans" code "SELL" from each of the funds. I then need to export these 4 selections to a database saved as "Fund XXX Sell Random Sample". Nothing needs to be in excel, everything can remain in IDEA.
I have watched your 10 part beginner's video and looked all over this site for a possible solution, but all seem to have minor hiccups. I cannot seem to figure out how to open or save multiple databases without going through each script and updating. I am very new to IDEA so any help is greatly appreciated.
Thanks,
Jon
jonnyperx
Offline
Last seen: 7 years 7 months ago
Joined: 09/22/2016 - 18:07
Hi Jon and welcome to the site.
Looks like you have an interesting problem but quite doable. I will see if I can put something together for you to check out and start off with.
What the script will have to do is loop through each file, then extract all the transactions with a trans code of SELL, then select 4 items and finally append them all together.
Probably the easiest way to do this is to assume that all the files are ones you want to perform the sampling on, so you would move all the files into a separate directory and then select it from the script.
I will see what I can come up with for you.
Brian
Hi Brian,
That is correct, all the files will be in a separate directory. As long as all the random samples are pulled from the "Sell" trans code, and there are 4 from each Fund, we should be good to go.
Thanks again for the help
Hi Jon,
Here is a demo for you to check out. It uses loops to read the files in a folder, the folder has to be under the project folder. It stores the file names in the sFiles() array. It then performs an extract on each file extracting all the Sell trans codes and stores the new filenames in the sExtractFileNames() array. It finally performs a random sample on each of the extracts and stores the file names in the sRandomFileNames() array. The final step is to take each of the random samples and combine them into one file.
Let me know if you have any questions. I have also attached the file for you.
Brian
Hi Brian,
Thank you so much for all the help thus far. Really appreciate it.
I have imported all my databases into a single folder as instructed above. When I run the script, I select the folder with the databases and it gives me an "Error on line 79 - Bad Equation provided" error. Do you have any tips on how to address such an error?
Thanks,
Jon
Hi Jon,
The script was just an example as I didn't have access to your database. The problem would be in this line where you are extracting the Sell items, most likely I don't have the correct field name. So modify this line and it should work.
"TRAN_CODE == ""Sell"""
If you are not sure of the equation perform the extraction on one of the files for all the Sell items and then go look in the history to see the proper syntax for the extraction.
Hope this helps.
Brian
Hi Brian,
Thank you for the quick response. I tweaked the names and it worked beautifully. You are a king.
Thanks again for the help. Really can't thank you enough!
Jon
Not a problem, glad to help.