Hello
I need to join 2 databases where the first has a date and the second has a period. The joining has to be done where the date of the first database is within the period of the second database.
So: database 1 has date 1 oct 2015, database 2 has 2 dates, date_from 5 jul 2015 and date_to 15 dec 2015 then the join is ok.
Has anyone any idea how to solve this in Idea, version 8 or 9?
Thanks !
Wim
wmollink
Offline
Last seen: 2 years 9 months ago
Joined: 03/12/2015 - 14:49
Hi Wim,
This is not an easy one. To do a join you need at least one common field between each file, this is not the case in your example. Now this could be done in a script. I am just tring to think if it is possible to create a virtual field that ends up being the common field. For the date range could it be any range? So could you have one record that the range is Jan 1 2015 to Feb 28, 2015 and another with Jan 1 2015 to March 31, 2015. If so how would the join work, would you select both these records or only the first one that is tested, much like regular joins?
Steve, any thought on how this could be done?
Thanks
Brian
Sorry, my fault.
It is like this:
File 1 contains an employercode, an employeecode and a date the employee had an accident or became sick. File 2 contains an employercode, an employeecode and a period the employee had a certain salary from that employer. If the employee got a promotion, a new record is added to file 2. An emplyee can have more than 1 job with different employers, but I want a new file which has all the records of file 1 and the salary the employee had at that moment from the employer in file 1. I also have to figure out a way to check file 2 for, I do not know the english word for it, periods that cross each other. It should not be possible to have 2 salaries from the same employer on a certain date.
I thought of sorting both files on employer- and employeecode and the date (for file 2 the starting date of the period). Then read a record from both files and compare first the employercode, if they are different, read the next record from the file with the lowest code, otherwise compare the employeecode and so on, but I am not sure about the best Ideacommands to use and how to create a new file with the fields form both files.
Hopefully this will make it clearer.
Thanks for looking at the problem and willing to help me.
Wim
Hi Wim,
Without having any data or IDEA in front of me I am thinking what you might want to do in IDEA is to do a join between the two files using the employercode and employeecode as the join keys, and selecting matches only. Now you can do an extract and I think there is a @between funciton in which you can extract all transaction in which the date of the accident is between the salary dates. You could then turn this into a script.
For the second problem. I would probably sort by Employercode, Employeecode and Salary Start. I would then do an extract using the @getnextvalue (I think that is the command) to compare it with the next record to see if they overlap. If I get a chance tonight I will try to make a scenario and figure out the equation for you.
Good luck.
Brian
Hello Brian
Thank you for your help. I really appreciate it!
I think your solutions will work. I am not at the office today, so I can't try them now.
For the first problem I think I have to join every emplyer/employeecode from file 1 with the matching codes in file 2 and the other way around. I am using the pc-version of version 8 at the moment and I do not think there is such an option there. We are starting with a test of Idea servcer version 9 in january and I have been told that that is one option that the serverversion supplies, so I will have to wait till the test starts. That is no problem.
The second solution I can try tomorrow. I will let you know the results.
Thanks again for your help. Especially your first solution saves me a lot of coding!
Wim
Glad to help, let me know how it goes. I had hoped to try it out last night but didn't get a chance.
Hello Brian
Both solutions worked !!!
For the first solution I had to use the visual connection and I had never used that before. I thought I had to wait for the serverversion, but it works also in the standalone version.
I found a few overlapping periods and that causes some problems, but to find those was the idea.
Thanks again for your input.
Wim
Hi Wim, glad you got everything working!!